Here is a handy trick to calculate last day of any month.
Assuming y and m contain the year & month for which you want to find the last day’s date, write
=DATE(y, m+1,0)
That is right, you can use ZERO (0) as the day.
When you do this, Excel tells us the last day of previous month.
How to calculate last day if I have a date in that month:
Instead of year and month, if you know a date & you want to find what is the last date… then use:
=EOMONTH(date, 0)
How to calculate the last working day of a month?
If you just care about working days.. then use:
=WORKDAY(DATE(y,m+1,1), -1)
This will give us the last working day of a month.
Dealing with holidays:
Very simple. Pass on a list of holidays to WORKDAY as last parameter. Like this:
=WORKDAY(DATE(y,m+1,1), -1, F1:F20)
This assumes, F1:F20 has a list of holiday dates.
Dealing with different weekend types:
Not everyone observes Saturday & Sunday as weekend. For example, I do not work on Tuesdays. You might have Sunday thru Thursday work week. If so,
use WORKDAY.INTL function, like this:
=WORKDAY.INTL(DATE(y,m+1,1), -1, 7)
Weekend type 7 is for Friday & Saturday weekend.
How do you calculate last date of a month?
Do you use some other formula to do this? Please share your tip using comments.
Work with dates often?
If you work with dates, then spend some time reading these tips:
4 Responses to “Office 2010 Contest Winners are here!!!”
I while ago I wrote a post on selecting a couple of names from a range via an UDF
I could have been handy.... especially because I didn't win.... lol
http://xlns.lamkamp.nl/?p=14
Sweet! I won! Thank you so much, Chandoo! I'm really speechless! I'll look out for an e-mail from you. Again, I really appreciate it, and I can't wait to fire it up!
Sincerely,
Tom "this one" 🙂
Thank You... Thank You... Thank You... 🙂
Hi,
Don't want to ruin your party.. 😉 but I noticed that when you sort the list A2:B11 (step 2), the RAND function re-calculates the numbers so that they are different and in mixed order again. I had to paste the whole area as values first and then sort to get it to work.
Wonder if the same happened to you because in your list at least Greg has a higher value than Tom 🙂