Microsoft Excel - Working With Dates Part 2
In Part 2 of working with dates we use the INT, DAY and WEEKDAY functions to derive the week number within a month for a given date. The following screen shot shows the desired results, assuming a week starts on a Monday:
Wednesday 1st June 2011 is in week 5 of the previous month (May) as the Monday of that week was ion the previous month.
Monday 6th June is in week 1 as it is the first Monday of June.
The WEEKDAY function can be used to return a number that represents the day (where 1 is Sunday) of the week for a date:
=WEEKDAY(A2)If you wish to see the name for the day of the week the formula will dop that for you:
=IF(C2=1,"Sunday",IF(C2=2,"Monday",IF(C2=3,"Tuesday",IF(C2=4,"Wednesday",IF(C2=5,"Thursday",IF(C2=6,"Friday",IF(C2=7,"Saturday")))))))
The DAY function can be used to return a number between 1 and 31 that represents the day of the month for a date:
=DAY(A2)
The INT function truncates a decimal number to return an integer. The following example would return 1:
=INT(1.675)
Question 2: How do I retrieve the week number within a month, where a week starts on a Monday?
The following formula will return the week number for a given date (the date in cell A2):
=INT((6+DAY(A2+1-WEEKDAY(A2-1)))/7)
If you would like to learn more about Microsoft Excel functions take a look at our Microsoft Office Excel Training Courses.