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:
9 Responses to “Show forecast values in a different color with this simple trick [charting]”
While this works in a pinch, it clearly "lightens" the colors of the entire chart. Depending on where you use this, it will be blatantly obvious that you don't know what you are doing and present a poor looking graph.
Why not separate the data into different segments when charting and have as many colors as you have data points? You might have to create a new legend and/or repeat the chart in "invisible ink", but it would be cleaner and more consistent when new or updated data becomes available.
While I think I agree that doing it "properly" via a second series is preferable, I don't necessarily agree that making the entirety of the "future" (data, gridlines, and even the axis) semi-transparent is "poor looking". I think it could be seen as adding more emphasis to the "future-ness" of the forecast data.
In short, it's another tool for the toolbox, even if it's never needed.
Simply and clever 🙂
Quick & effective, cool. thanks.
I always use the dummy series.
Nice little trick, thanks very much!
Two sets of data better. Control is much better.
You can use the same chart next month to see what is actual and what is forecast.
To use this trick, I think grid lines has to be removed, that will make the graphic much more sharp.
to be honest, i dont understand why there is needed to do this way... in this case horizontal lines will be pale as well. then why a just can't change the color of the line partly???
Great tutorial. Thanks for the tutorial!