| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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...  -- forevercalz ------------------------------------------------------------------------ forevercalz's Profile: http://www.excelforum.com/member.php...o&userid=28931 View this thread: http://www.excelforum.com/showthread...hreadid=490649 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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-- forevercal ----------------------------------------------------------------------- forevercalz's Profile: http://www.excelforum.com/member.php...fo&userid=2893 View this thread: http://www.excelforum.com/showthread.php?threadid=49064 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ??? -- forevercalz ------------------------------------------------------------------------ forevercalz's Profile: http://www.excelforum.com/member.php...o&userid=28931 View this thread: http://www.excelforum.com/showthread...hreadid=490649 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ??? 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference betwen Excel Date () Function and System Date | Excel Worksheet Functions | |||
| Difference System date and Excel Date function | Excel Worksheet Functions | |||
| Date Difference | Excel Discussion (Misc queries) | |||
| Difference between last date of month and given date | Excel Worksheet Functions | |||
| Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
![forevercalz[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
