Getting Date difference....help (difficult)

[forevercalz]

i have this column B named
Update Time
2005080112:22:35
2005080112:46:20
2005080200:30:21

and i have column F named
New Shift end
20050801 20:30
20050801 19:30
20050803 18:00

well i need to find the date difference between this 2 time.....Column
F minus Column B.. i need to get this difference in column H....

for eg.. 20050801 20:30 - 2005080112:22:35 will give me 7:57:55..
for eg... 20050803 18:00 - 2005080200:30:21 will give me 41:29:39 (its
a day more so need to plus 24 hours)

both column B and F are in string format.....any help will be greatly
appreciated!

 

[Tom Ogilvy]

Dim rng as Range, yr as Long
Dim mo as Long, dy as Long
Dim tm as Date, dt as Date
Dim yr1 as Long, dt1 as Date
Dim mo1 as Long, dy1 as Long
Dim tm1 as Date

set rng = Range(cells(1,2),cells(1,2).End(xldown))

for each cell in rng
yr = clng(Left(cell,1,4))
mo = clng(Mid(cell,5,2))
dy = clng(Mid(cell,7,2))
tm = TimeValue(Mid(cell,9,8))
set cell1 = cell.offset(0,4)
yr1 = clng(Left(cell1,1,4))
mo1 = clng(Mid(cell1,5,2))
dy1 = clng(Mid(cell1,7,2))
tm1 = TimeValue(Mid(cell1,10,5))
dt = DateSerial(yr,mo,dy) & tm
dt1 = DateSerial(yr1,mo1,dy1) & tm1
cell1.offset(0,2).Value = dt1 - dt
cell1.offset(0,2).Numberformat = "[hh]:mm"
Next


--
Regards,
Tom Ogilvy

"forevercalz" <[email protected]>
wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

i have this column B named
Update Time
2005080112:22:35
2005080112:46:20
2005080200:30:21

and i have column F named
New Shift end
20050801 20:30
20050801 19:30
20050803 18:00

well i need to find the date difference between this 2 time.....Column
F minus Column B.. i need to get this difference in column H....

for eg.. 20050801 20:30 - 2005080112:22:35 will give me 7:57:55..
for eg... 20050803 18:00 - 2005080200:30:21 will give me 41:29:39 (its
a day more so need to plus 24 hours)

both column B and F are in string format.....any help will be greatly
appreciated!

You can also do this with a worksheet formula:

=DATE(LEFT(F2,4),MID(F2,5,2),MID(F2,7,2))+TIMEVALUE(RIGHT(F2,5))-
DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))-TIMEVALUE(RIGHT(B2,8))


--ron

 

[forevercalz]

hi...im a newbie to this...
may i ask..where do i paste the above codes in my excel worksheet?
does i paste it when i right clicked on view code
and place it under
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)???

can someone guide me? i quite lost...

 

[forevercalz]

You can also do this with a worksheet formula:

=DATE(LEFT(F2,4),MID(F2,5,2),MID(F2,7,2))+TIMEVALUE(RIGHT(F2,5))-
DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))-TIMEVALUE(RIGHT(B2,8))


--ron

ok ron..i tried ur code
and i get this answer 0.338483796
this is for the first example...20050801 20:30 - 2005080112:22:3
...how come it gives me
0.338483796 ??? :confused

 

[Tom Ogilvy]

Just format the cell containing the result using a custom number format of

[hh]:mm

the number you see is how excel stores times/dates.

--
Regards,
Tom Ogilvy


"forevercalz" <[email protected]>
wrote in message

 

[Ron Rosenfeld]

ok ron..i tried ur code
and i get this answer 0.338483796
this is for the first example...20050801 20:30 - 2005080112:22:35
..how come it gives me
0.338483796 ???

That's how Excel stores the data.

To see it in an easily understood format, from the top menu bar select:

Format/Cells/Number Custom Type: [h]:mm


--ron