Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Diff from text format | Excel Worksheet Functions | |||
How do I set up a tennis schedule.same time, same day diff week? | Charts and Charting in Excel | |||
Diff. in time | Excel Worksheet Functions | |||
Diff. in time | Excel Worksheet Functions | |||
Diff. in time | Excel Worksheet Functions |