time diff across DST without VBA?
 

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

time diff across DST without VBA?
 

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.

"George" wrote:

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

time diff across DST without VBA?
 

On Sun, 2 Nov 2008 04:27:00 -0800, Joel
wrote:

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.

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


"George" wrote:

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

time diff across DST without VBA?
 

Sorry, but what is DST?


--
Pecoflyer
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24197

time diff across DST without VBA?
 

On Sun, 2 Nov 2008 12:57:05 +0000, Pecoflyer
wrote:


Sorry, but what is DST?

Daylight savings time. US clocks are set back by 1 hr on 1st Sun in
Nov.

G

time diff across DST without VBA?
 

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


"George" wrote:

On Sun, 2 Nov 2008 04:27:00 -0800, Joel
wrote:

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.

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


"George" wrote:

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

time diff across DST without VBA?
 

On Sun, 2 Nov 2008 12:57:05 +0000, Pecoflyer
wrote:


Sorry, but what is DST?

.... 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

time diff across DST without VBA?
 

A1 = 11/2/08 1:00 AM

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


"George" wrote:

On Sun, 2 Nov 2008 04:27:00 -0800, Joel
wrote:

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.

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


"George" wrote:

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

time diff across DST without VBA?
 

Ironic? No, in Europe we do the same but on the last week end of October


--
Pecoflyer
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24197

time diff across DST without VBA?
 

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.

--
Thanks,
Shane Devenshire


"George" wrote:

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