How to return the Month-to-Date day of current month in Excel within a single formula?

0
votes

I did the following in excel to get the MTD day of current month - i.e. if today is 31 march 2020, I should get 30 as number of days in the MTD.

Im wondering whether this can be done in a single formula within the need of having =today() formula to get the current date first and separately use =day()-1 to get the MTD days? - this case would be 2 separate formulas.

enter image description here

2
excelgoogle-sheetsexcel-formulagoogle-sheets-formuladays
Try =DAY(TODAY())-1. – Enigmativity
See Excel function EOMONTH.. EOMONTH(Today(),-1) will give you previous months last date. So Today()-EOMONTH(Today(),-1)-1 is what you are looking for – Naresh

2 Answers

0
votes

try like this in Google Sheets:

=EOMONTH(TODAY(), 0)
0
votes

To get the date 1 day before the current date, i.e. the MTD days that have completed you could use this: 

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-1)

However, what is the MTD on April 1, 2020? It'd be april fools I guess becuase their is no April 0, 2020. To get around this you could use: 

=DATE(YEAR(TODAY()),MONTH(TODAY()),IF(DAY(TODAY())-1>0,DAY(TODAY())-1,DAY(TODAY())))

Admittedly the second one is pretty messy.