CODEWITHSUNDEEP
exceldatetimeexcel-2007
user66001
user66001

Reputation: 935

How to mathematically extract milliseconds, seconds and minutes from a serial date with an Excel-formula?

How can one calculate the seconds in a Excel serial date/time, linke in the example below, without the use of the SECOND() function which (I believe) rounds or cell-formatting? How can i extend this for minutes?

The serial values for the bottom example below (last row in the image) are:

     Right: 41165.4444365394
     Left: 41165.4444321412

The above example is being conditional formatted with the below two formula (With stop if true turned on, and order of precedence shown).

Seems to me that the second function cannot see the difference between 10:39:58 and 10:39:59, hence is not matching the 'Display only seconds' resolution one, and is carrying on to the 'Display milliseconds resolution' given that the calculation I have for milliseconds works fine.

    To see if the seconds are different, first: =SECOND($B2)<>SECOND($C2)
    To see if only milliseconds have passed between the two times:
    =ROUND(($B2*86400-INT($B2*86400))*1000,0)<>ROUND(($C2*86400-INT($C2*86400))*1000,0)

What am I to multiply the serial number against to provide the unrounded seconds and minutes (seperately)?

Upvotes: 1

Views: 5929

Answers (2)

Jook
Jook

Reputation: 4682

You were almost there, try it with this:

B2 = 41165.4444365394
C2 = 41165.4444321412

=(VALUE(TEXT(B2,"ss.000"))-ROUND(MOD(B2*86400,INT(B2*86400)),3))<>(VALUE(TEXT(C2,"ss.000"))-ROUND(MOD(C2*86400,INT(C2*86400)),3)


VALUE(TEXT(B2,"ss.000")) -> gives you 59,317

VALUE(TEXT(C2,"ss.000")) -> gives you 58,937

MOD(B2*86400,INT(B2*86400)) 
-> is more appropriate here than substracting, but basically the same thing

edit: using only math

i am not 100% sure about this, but it is not as straight forward with the time-values as it was for millisecs, and this is why:

excel interprets a value like 1.00001 as the January 1. 1900 00:00:01.

the part before . is the date, the part after is the time.

unfortunately time runs between .00000 and .99999, but your average day has only 86400 seconds - so 0.0000115740740740741 is the factor between them.

By dividing 0.00038 with this factor or multiplying it with 86400 you get 32.832 - which rounds to 33 seconds, as excel would do. You can use this to get the number of seconds in the given time.

edit:

here is my end solution for now

=INT(MOD(B2,INT(B2))/0.0000115740740740741))

this will transform you any time into seconds, so that you can compare by them without text formats - like this:

  =INT(MOD(B2,INT(B2))/0.0000115740740740741))<>INT(MOD(C2,INT(C2))/0,0000115740740740741))

you don't have to worry about the ms, they will cut off by INT.

oh, and here is my source, that gave me the right clue:

http://support.microsoft.com/kb/214094/EN-US

edit thanks to barry houdini, I'll add some additional informations:

=INT(MOD(B2,INT(B2))/0.0000115740740740741)
=INT(MOD(B2,INT(B2))*86400)
=INT(MOD(B2,1)*86400)
-> 38399 seconds of the day, without ms
-> shows only time differences regardless of date
=MOD(B2,1)*86400
-> 38399.317 seconds of the day, with ms
=INT(B2*86400)
-> 3556697399 seconds since 1.1.1900
-> shows time & date differences

=INT(MOD(B2*24*60*60,60)) (=INT(MOD(B2*86400,60)))
-> 59 seconds, regardless of time & date, only the current seconds part
=INT(MOD(B2*24*60,60))
-> 39 minutes
=INT(MOD(B2*24,60))
-> 10 hours

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

Try using FLOOR function to differentiate, i.e.

=FLOOR($B2,"0:00:01")<>FLOOR($C2,"0:00:01")

This will also distinguish between times when, say, the seconds are the same but the minutes are different.

Edit: this will do the same

=INT($B2*86400)<>INT($C2*86400)

Upvotes: 1

Related Questions