Excel tip on Eomonth,Today and workday function

Last modified date

Below syntax of functions

  • EOMONTH(date, default is zero) 
  • TODAY() gives today’s date Remember this is a Volatile function which changes every day as per system dates.
  • WORKDAY(strat_date, days, [holidays]) this will help to calculate the next working day it will ignore Saturday and Sunday by default. If you have any holidays other than these 2 days then you can give in 3rd argument(update the of holiday dates and select that entire range.)
  • DATE(year, month, day) this is simple date function input year, month & day

Combination of both we see the results below

=EOMONTH(TODAY(),0):- IF you use this Function you can get end date of the current month.

Now we see how to create the list of business dates only First and last business dates for the given year

Below Functions used:

  1. EOMONTH
  2. WORKDAY
  3. DATE

=DATE(B2,1,1) This will help to make the January 1st date for the required year.

Cell B2 is the reference cell where we can enter the required year.

=EOMONTH(D3,0)+1 This will help to create all month beginning dates which help us to get first and last business dates.

=WORKDAY(EOMONTH(D3,-1),1,H3:H14) This will help to create the first business date.

=WORKDAY(EOMONTH(D3,0),-1,H3:H14) This will help to create the last business date.

In H4:H14 we have listed the Holiday dates and in I Column we have the holiday names.

Below screen prints will help you to understand more on this.


Click to download the copy

Leave a Reply

Your email address will not be published. Required fields are marked *

Post comment