The month is very important when I am doing reporting because most of the time, I need to know what happened during the month or during the previous month or to forecast, to the next month so to display correctly the month, it is crucial.

When I use the formula ?

In all of my monthly reports, the month is important.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is/are the formula(s) ?

=DATE()

=YEAR()

=MONTH()

=DAY()

=NOW()

This is the general formula showing the current day:

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))

Now, if I want to display a specific day, month or year, just remove “DAY(NOW())”, “MONTH(NOW())” or “YEAR(NOW())” by its corresponding value. To display the day 11 with the current month and year:

=DATE(YEAR(NOW()),MONTH(NOW()),11)

To display March with the current year and day:

=DATE(YEAR(NOW()),3,DAY(NOW()))

To display 2011 with the current month and day:

=DATE(2011,MONTH(NOW()),DAY(NOW()))

The formulas below are the ones I used the most for my reports so to display the beginning of the month:

=DATE(YEAR(NOW()),MONTH(NOW())-1,1)

To display the end of the month

=DATE(YEAR(NOW()),MONTH(NOW()),1)-1

Since I don’t specify the time, the formula will take as granted that the hour starts at 00:00 so sometimes, for the end information, I have to put this formula:

=DATE(YEAR(NOW()),MONTH(NOW()),1)-1+TIME(23,59,0)

This is particular useful for the last day because sometimes I have to do some calculation with the date and if I don’t put the time, the formula will not take anything that started after 00:00 meaning that in fact, my last day of the month is, for this example, the 30.05.2019 at 23h59.

A workaround is to put for the last day, the first day of the next month with this formula:

=DATE(YEAR(NOW()),MONTH(NOW()),1)