time diff across DST without VBA?

G

George

Can I (easily) get a simple difference between two time-format cells to
include the additional/missing hour across the DST change?

(Apologies if this is really old. I did google it.)

Thanks,
George
 
J

Joel

If you are subtracting two times then simply format the column with the
difference to any time/Date format as you like.

There are also time/date formats under custom. Excel will normally truncate
hours to 24 when the times are larger than a day. You can use [h]:mm:ss to
get hours greater than 24.
 
G

George

If you are subtracting two times then simply format the column with the
difference to any time/Date format as you like.

There are also time/date formats under custom. Excel will normally truncate
hours to 24 when the times are larger than a day. You can use [h]:mm:ss to
get hours greater than 24.
Click to expand...

Either my question wasn't clear, or I'm not following you. I subtract
two time/date cells ...
(11/02/08 6:28:17), (11/1/08 23:17)
using (say) ...
24*(A5-A6)
to get the result in hours.

This normally works fine; but, it misses the DST change. So, in this
case, it gives 7.2 (hrs), instead of 8.2.

G
 
G

Gary''s Student

Use the GMT time reference.
DST will not be an issue.
--
Gary''s Student - gsnu200811


George said:
If you are subtracting two times then simply format the column with the
difference to any time/Date format as you like.

There are also time/date formats under custom. Excel will normally truncate
hours to 24 when the times are larger than a day. You can use [h]:mm:ss to
get hours greater than 24.
Click to expand...

Either my question wasn't clear, or I'm not following you. I subtract
two time/date cells ...
(11/02/08 6:28:17), (11/1/08 23:17)
using (say) ...
24*(A5-A6)
to get the result in hours.

This normally works fine; but, it misses the DST change. So, in this
case, it gives 7.2 (hrs), instead of 8.2.

G
Click to expand...
 
G

George

Sorry, but what is DST?
Click to expand...

.... or were you just being ironic? I have trouble recognizing that. I
know someone who codes everything in GMT. He's quite proud of it. User
interface issues are just unworthy users. I miss a lot of his irony,
too.

G
 
J

Joel

A1 = 11/2/08 1:00 AM

if(And(A5<A1,A6>A1),A6-A1+1,A6-A5)


George said:
If you are subtracting two times then simply format the column with the
difference to any time/Date format as you like.

There are also time/date formats under custom. Excel will normally truncate
hours to 24 when the times are larger than a day. You can use [h]:mm:ss to
get hours greater than 24.
Click to expand...

Either my question wasn't clear, or I'm not following you. I subtract
two time/date cells ...
(11/02/08 6:28:17), (11/1/08 23:17)
using (say) ...
24*(A5-A6)
to get the result in hours.

This normally works fine; but, it misses the DST change. So, in this
case, it gives 7.2 (hrs), instead of 8.2.

G
Click to expand...
 
S

ShaneDevenshire

Hi,

You might try something like this

=24*IF(TRUNC(A5,0)>DATE(YEAR(A5),10,31),1/24+A5-A6,A5-A6)

Your adjustment will depend which time is in DST and which not.
 
Top