1

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)?

dsolimano
  • 8,870
  • 3
  • 48
  • 63
user66001
  • 774
  • 1
  • 13
  • 36
  • can you clarify again: do you wan't the unrouded minutes or seconds? Is this the date you're working on: 13.09.2012 10:39:59? and are 371 the ms you calculated or is it 3556694399.371, which is what I get by your formula. – Jook Sep 20 '12 at 07:51
  • would mind to expand, if you are working with formulars or with VBA or both and give a little more detail on the whole process? you know you could just "ss" as the cell-format i.e. to display only the seconds - or "ss.000" to display seconds & ms. – Jook Sep 20 '12 at 08:08
  • by the way, got it from here: http://stackoverflow.com/questions/3095407/display-milliseconds-in-excel – Jook Sep 20 '12 at 08:18
  • @Jook - I cannot use VBA. I will edit my post. – user66001 Sep 20 '12 at 12:13

2 Answers2

1

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)

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • the part with the "0:00:01" -> shouldn't this be the number of parts to round to? - otherwise, nice, i knew i was missing some point there ^^ – Jook Sep 20 '12 at 16:41
  • Thanks Jook, "0:00:01" is a time value - 1 second - so using `FLOOR` with that value rounds the time down to the previous whole second, hence it will distinguish between `12:34:56.999` and `12:34:57.001`. Equally you could use `CEILING` – barry houdini Sep 20 '12 at 16:46
  • .....also you could use a shorter version with INT....edited my answer to that effect – barry houdini Sep 20 '12 at 16:51
  • yeah, but like that you'll end up using the whole value, wich consists of date&time - therefore you'll end up, like in the formular of my latest edit ;) - but using *86400 is nicer than dividing by my factor ^^ – Jook Sep 20 '12 at 16:56
  • No, I don't see the problem - both of those formulas do the same thing, if the first one works then so will the second.... – barry houdini Sep 20 '12 at 16:58
  • ah - yeah, ok - including the date might be even better, you're right. – Jook Sep 20 '12 at 17:01
  • @barry houdini - Thanks. I like the simplicity of your solution, but want to get my head around the way Jook is heading down. Also, =INT($B2*86400) on 41165.4444321412 gives me 3556694398. What part of that is the seconds? – user66001 Sep 20 '12 at 17:23
  • When you multiply by 86400 you get the total seconds, including the date (so 3556694398 is the number of seconds since 1st Jan 1900!). I was assuming that you want to check whether it's the exact same second or not, in which case that works OK - if you just compare the seconds within the day isn't it possible that they match......but they are on different days? - for seconds on the day you can use `=INT(MOD($B2,1)*86400)` – barry houdini Sep 20 '12 at 17:53
  • http://i.imgur.com/B8swk.png Hopefully this will make it easier to see what I am getting. – user66001 Sep 20 '12 at 18:05
  • 1
    Can I just clarify? Is my suggested formula working for what you need or do you need it to do something different...or are you just trying to understand how it works? Your date/time in B10 is stored as a number, in this case `41162.3726578357` the integer part is the date (count of days since 1/1/1900) and the fractional part is the time. If you use =MOD(B10,1) that extracts just the fractional part, i.e. 0.3726578357, then multiply that by 86400 gives you the seconds in the day (since midnight), i.e. 32197.64 - INT then just rounds that down to 32197. – barry houdini Sep 20 '12 at 18:38
  • @barryhoudini - Ahhhh, right. I can see the confusion now. I meant extract the seconds the date represents, not the amount of seconds that passed since midnight. I think I can figure this out from here. Will post back if not, and elect an answer (Though not sure which one now, as the later comments of this answer is similar to the path Jook was going in the other answer) if so. Thanks again guys! – user66001 Sep 20 '12 at 22:12
  • no problem - you can take your time selecting an answer, not an issue for me - "extract the seconds the date represents". I think soem concrete examples would help but I assume that you mean, if you have 1-Sep-2012 13:42:57.99 you want to get the result 57 and for 1-Sep-2012 10:23:41.01 you want 41. That's quite simple but I assumed that you wanted to compare two times to see if they were on the same second? If so ten surely there can be confusion selecting just that part - two times could have the same seconds but be different minutes. – barry houdini Sep 20 '12 at 22:43
  • 1
    To extract 57 and 41 from the above, either use this version `=SECOND($B2-1/86400/2)` - which deducts half a second from your time value then takes the SECOND value, the deduction counteracts the inherent rounding of SECOND....or use this formula `=INT(MOD($B2*86400,60))` - B2*86400 gets the total seconds, MOD gives you the reainder when dividing by 60, effectively removing minutes, INT again to round down – barry houdini Sep 20 '12 at 22:52
  • @houdini - ha, MOD by 60 -> great, this I could not come up with yesterday - but why do you substract B2*86400 afterwards?! `=INT(MOD(41165.4444321412 * 86400,60))` already results to 58. – Jook Sep 21 '12 at 08:28
  • That's right @Jook, the formula is just the `=INT(MOD($B2*86400,60))` part - the next bit was part of my explanation "B2*86400 gets the totals seconds", sorry for confusion – barry houdini Sep 21 '12 at 09:49
  • Thanks guys. Wish I could pick both of the answers as being right, as you have both been very helpful. barryhoudini, as you said you don't mind, and you have far more (helpful I am sure) reputation, I will pick Jook's answer as being "correct", but trust others with this same question will read all comments and be very helped by them. – user66001 Sep 22 '12 at 16:04
0

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
Jook
  • 4,564
  • 3
  • 26
  • 53
  • Thanks, but I wanted to learn how to calculate seconds without the use of formatting functions. As for Mod, I have read (which are not handy right now) threads on how it fails when used with some form of date (Can't recall the specifics, but hopefully you know what I mean). There must be a way to calculate this with pure maths, no? – user66001 Sep 20 '12 at 14:41
  • Thanks @Jook. I follow up until you introduce 0,00038 into the explanation. Is this an arbitary value, or arrived at through the previous step? – user66001 Sep 20 '12 at 16:08
  • yes - i tested this by setting up two cols with values from 0,00001 to 0,99999 - and formatted them - one as number, one as time. then picked 0,00038 to test my formulars with it. – Jook Sep 20 '12 at 16:10
  • Thanks again, I think you are almost there, but when I c&p =ROUND(MOD(B2,INT(B2))/0,0000115740740740741)),0) into the spreadsheet as a test, it is complaining (I think about the , in 0,0000115740740740741, though I can't figure out how to fix it. – user66001 Sep 20 '12 at 16:36
  • oh, yeah - your english it has to be `.` not `,` in 0,00001 - but houdini astonished me with an even nicer solution to this – Jook Sep 20 '12 at 16:39
  • had to replace ROUND by INT - otherwise this would be the same like using 'SECOND()' – Jook Sep 20 '12 at 16:57
  • Okay, if B2 = 41165.4444321412 and I put this in a cell =ROUND((MOD(B18,INT(B18))/0.0000115740740740741),0) (Note the changes in bracket location, as there were errors otherwise), I get 38399. What's wrong? – user66001 Sep 20 '12 at 17:19
  • =INT(MOD(B2,INT(B2))/0,0000115740740740741)) on 41165.4444321412 gives me 39548. No two number sequential combination of that is the seconds of the formatted date that is 41165.4444321412. – user66001 Sep 20 '12 at 17:25
  • @Jook, Note that for extracting MINUTE or HOUR, those exact functions (MINUTE or HOUR) should work OK - they don't round like SECOND does – barry houdini Sep 22 '12 at 16:07