Displaying calculated date & time

[JonathanMoore]

Hi all,

I've got the following formula to display the 1st day of a given month:

DATE(YEAR(TODAY()),MONTH(TODAY()),1), EOMONTH(TODAY(),0)+1)

and it is displayed using the following format:

20120101 0000 - yyyymmdd hhmm

My users' (god bless them) have decided that they want the time to be 0001 not 0000... is it possible to amend my formula to display the 1st day of a given month at 1 minute past midnight (0000)?

Thanks in advance,


- Jon

[mohd9876]

you can add 1 minute to you formula as follows:

DATE(YEAR(TODAY()),MONTH(TODAY()),1)+TIME(0,1,0)

[JonathanMoore]

Thats great - thanks for that!

Timely help as always!!

[daddylonglegs]

It's not clear to me what your original formula is doing but if you can use EOMONTH as you have done there then this will give you 1 minute after midnight on the first of the current month

=EOMONTH(TODAY(),-1)+"24:01"