datetime minus 1 sec issue

[amien]

I have this in a textbox:

=DATE(42397.3210243,'DD-MMM-YYYY HH:MM:SS')

This will give me : 28-JAN-2016 07:01:16 (great!)

Now 1 second less:

=DATE(42397.3210243- (1/(24*60*60)),'DD-MMM-YYYY HH:MM:SS')

This will give me : 28-JAN-2016 07:01:15 (great!)

Now 17 second less:

=DATE(42397.3210243- (17/(24*60*60)),'DD-MMM-YYYY HH:MM:SS')

This will give me : 28-JAN-2016 07:01:59 (Not good)

i would expect : 07:00:59 instead of 07:01:59

What i'm doing wrong?

[sunny_talwar · Accepted solution]

Try this:

=DATE(42397.3210243- (17/(24*60*60)),'DD-MMM-YYYY hh:mm:ss')

MM is for Months

mm is for Minutes

You used MM for minutes as well

[#3]

Your time format is wrong, As Sunny told use hh:mm:ss

Timestamp(42397.3210243- (17/(24*60*60)),'DD-MMM-YYYY hh:mm:ss')

I would prefar Timestamp instead of date function for understand purpose.

[amien]

Thanks Both