Solved

determining the ordernal number of a certain week day.

Posted on 2001-07-23
8
289 Views
Last Modified: 2010-05-18
How can I determining the ordernal number of a certain week day?

I mean, funtion(today()) should return 4. because today(7.23) is the 4th monday of this month.

and the number should be set unless a user saves.

Thanks.
0
Comment
Question by:iamjhkang
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 13

Accepted Solution

by:
cri earned 50 total points
ID: 6307651
Use WEEKDAY(serial_number,return_type)

See Excel Help regarding 'return_type' which defines the start of the week.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6307745
To get the right day name with the weekday function, you can include it in an index function:

example:

=INDEX({"So","Mo","Tu","We","Th","Fr","Za"},1,WEEKDAY(TODAY()))


GL
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 6350781
jamjhkang,

If I understand your request correctly you want to know if a certain date is the first, second, third, fourth or fifth of a particular weekday of the month.  The following formula will accomplish the task.  However, you will need to activate the Analysis ToolPak thru the Tools, Add-ins menu option.

=IF(WEEKDAY(B1)=WEEKDAY(EOMONTH(B1,-1)),INT((B1-EOMONTH(B1,-1))/7),INT((B1-EOMONTH(B1,-1))/7)+1)

where B1 is the cell that you entered the date you want to analyze.

Hope this helps.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6351240
What do you mean by "because today(7.23) is the 4th monday of this month"

Do you like to have:
- Daynumber of the week,
- Daynumber of the month
- Daynumber of the year
- Weeknumber of the month
- Weeknumber of the year.
- Month number of the year

Tell me, looking at the given comments, it's not very clear.

0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 6467027
Is more needed?
Asta
0
 

Expert Comment

by:amp072397
ID: 6706550
iamjhkang, how are you?

I know the email notifications were working erratically at best before, but they work great now. If you don't respond within 7 days, I'll be awarding points to cri. Other answers may be valid, but he posted first.

amp
Community Support Moderator
0
 

Expert Comment

by:amp072397
ID: 6719568
Awarding points to cri.

amp
community support moderator
0
 
LVL 13

Expert Comment

by:cri
ID: 6721663
amp, thank you.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question