Getting Date difference....help (difficult)
 

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! ;)


--
forevercalz
------------------------------------------------------------------------
forevercalz's Profile: http://www.excelforum.com/member.php...o&userid=28931
View this thread: http://www.excelforum.com/showthread...hreadid=490649

Getting Date difference....help (difficult)
 

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"
wrote in message
...

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! ;)


--
forevercalz
------------------------------------------------------------------------
forevercalz's Profile:
http://www.excelforum.com/member.php...o&userid=28931
View this thread: http://www.excelforum.com/showthread...hreadid=490649

Getting Date difference....help (difficult)
 

On Sun, 4 Dec 2005 21:36:09 -0600, forevercalz
wrote:


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))+TIMEVALU E(RIGHT(F2,5))-
DATE(LEFT(B2,4),MID(B2,5,2),MID(B2,7,2))-TIMEVALUE(RIGHT(B2,8))


--ron

Getting Date difference....help (difficult)
 

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


--
forevercalz
------------------------------------------------------------------------
forevercalz's Profile: http://www.excelforum.com/member.php...o&userid=28931
View this thread: http://www.excelforum.com/showthread...hreadid=490649

Getting Date difference....help (difficult)
 

Ron Rosenfeld Wrote:
On Sun, 4 Dec 2005 21:36:09 -0600, forevercalz
wrote:


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
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))+TIMEVALU E(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: :confused: :confused: :confused

--
forevercal
-----------------------------------------------------------------------
forevercalz's Profile: http://www.excelforum.com/member.php...fo&userid=2893
View this thread: http://www.excelforum.com/showthread.php?threadid=49064

Getting Date difference....help (difficult)
 

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"
wrote in message
...

Ron Rosenfeld Wrote:
On Sun, 4 Dec 2005 21:36:09 -0600, forevercalz
wrote:


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))+TIMEVALU E(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:35
..how come it gives me
0.338483796 ???:confused: :confused: :confused: :confused:


--
forevercalz
------------------------------------------------------------------------
forevercalz's Profile:
http://www.excelforum.com/member.php...o&userid=28931
View this thread: http://www.excelforum.com/showthread...hreadid=490649

Getting Date difference....help (difficult)
 

On Sun, 4 Dec 2005 23:39:15 -0600, forevercalz
wrote:


Ron Rosenfeld Wrote:
On Sun, 4 Dec 2005 21:36:09 -0600, forevercalz
wrote:


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))+TIMEVALU E(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:35
..how come it gives me
0.338483796 ???:confused: :confused: :confused: :confused:

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