x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 358

# determining the ordernal number of a certain week day.

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
iamjhkang
• 2
• 2
• 2
• +2
1 Solution

Commented:
Use WEEKDAY(serial_number,return_type)

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

Application ConsultantCommented:
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

Commented:
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

Application ConsultantCommented:
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

Commented:
Is more needed?
Asta
0

Commented:
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

Commented:
Awarding points to cri.

amp
community support moderator
0

Commented:
amp, thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.