Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Convert time value to a number
Hello,
I have a column that contains cell values of various times, in another column, I need to change those time values to numbers. Sheet snippet: COL A COL B COL C COL D COL E COL F Date Time Strt Time End Total Hours for Day 70 Hr Clock Hours Remaining ROW 1 9/1/2007 8:00 14:00 6:00 6.00 70 - 41.00 70 minues Col E running tally ROW 2 9/2/2007 7:00 15:30 8:30 14.50 or ROW 3 9/3/2007 8:00 15:30 7:30 22.00 29.00 ROW 4 9/4/2007 7:00 15:30 8:30 30.50 ROW 5 9/5/2007 0:00 0:00 0:00 30.50 ROW 6 9/6/2007 0:00 0:00 0:00 30.50 ROW 7 9/7/2007 6:00 14:30 8:30 39.00 ROW 8 9/8/2007 6:00 14:30 8:30 47.50 ROW 9 9/9/2007 7:00 15:30 8:30 47.50 Drops R2 Col E ROW 10 9/10/2007 0:00 0:00 0:00 40.00 Drops R3 Col E ROW 11 9/11/2007 6:00 14:30 8:30 41.00 Drops R4 Col E Col D needs to be in numeric form with 2 decimals and has calculation of Col C - Col B Col E needs to tally Col D, but in 8 day cycles. For this posting, Col D and Col E were added manually to show how it is supposed to look. What I'm looking for is functions that will convert the time values into numbers with 2 decimal places and then, for Col E, to keep a running tally of Col D, but only in 8 day cycles and then in Col F, a calculation to keep track of remaining "time" (70 - latest Col E amount). It's a lot and I'm not sure if it's feasible....but any help would be appreciated. Don |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Convert time value to a number
If column D is your total hours for the day, or C-B, then it looks to me as
if it's not "2 decimals" but time format [h]:mm. You've got 8:30, not 8.50. The formula is simply =C1-B1 unless you need to wrap round over midnight, in which case it would be =MOD(C1-B1,1). For column E, I guess you want =SUM(INDIRECT("D"&MAX(ROW(D1)-7,1)):D1)*24 and format as number with 2 decimal places. I notice that in row 9 you say drop R2 Col E, but doesn't it drop R1 Col D, & similarly for the subsequent rows? I therefore get a different answer from yours from there onwards. Did I misunderstand? -- David Biddulph "DW" wrote in message ... Hello, I have a column that contains cell values of various times, in another column, I need to change those time values to numbers. Sheet snippet: COL A COL B COL C COL D COL E COL F Date Time Strt Time End Total Hours for Day 70 Hr Clock Hours Remaining ROW 1 9/1/2007 8:00 14:00 6:00 6.00 70 - 41.00 70 minues Col E running tally ROW 2 9/2/2007 7:00 15:30 8:30 14.50 or ROW 3 9/3/2007 8:00 15:30 7:30 22.00 29.00 ROW 4 9/4/2007 7:00 15:30 8:30 30.50 ROW 5 9/5/2007 0:00 0:00 0:00 30.50 ROW 6 9/6/2007 0:00 0:00 0:00 30.50 ROW 7 9/7/2007 6:00 14:30 8:30 39.00 ROW 8 9/8/2007 6:00 14:30 8:30 47.50 ROW 9 9/9/2007 7:00 15:30 8:30 47.50 Drops R2 Col E ROW 10 9/10/2007 0:00 0:00 0:00 40.00 Drops R3 Col E ROW 11 9/11/2007 6:00 14:30 8:30 41.00 Drops R4 Col E Col D needs to be in numeric form with 2 decimals and has calculation of Col C - Col B Col E needs to tally Col D, but in 8 day cycles. For this posting, Col D and Col E were added manually to show how it is supposed to look. What I'm looking for is functions that will convert the time values into numbers with 2 decimal places and then, for Col E, to keep a running tally of Col D, but only in 8 day cycles and then in Col F, a calculation to keep track of remaining "time" (70 - latest Col E amount). It's a lot and I'm not sure if it's feasible....but any help would be appreciated. Don |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to: Convert time value to a number
David,
Ooops, yes, that was a mistype. At Row 9, the calculation should drop Row 1, then at Row 10 drop Row 2, Row 11 drop Row 3, etc. "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... If column D is your total hours for the day, or C-B, then it looks to me as if it's not "2 decimals" but time format [h]:mm. You've got 8:30, not 8.50. The formula is simply =C1-B1 unless you need to wrap round over midnight, in which case it would be =MOD(C1-B1,1). For column E, I guess you want =SUM(INDIRECT("D"&MAX(ROW(D1)-7,1)):D1)*24 and format as number with 2 decimal places. I notice that in row 9 you say drop R2 Col E, but doesn't it drop R1 Col D, & similarly for the subsequent rows? I therefore get a different answer from yours from there onwards. Did I misunderstand? -- David Biddulph "DW" wrote in message ... Hello, I have a column that contains cell values of various times, in another column, I need to change those time values to numbers. Sheet snippet: COL A COL B COL C COL D COL E COL F Date Time Strt Time End Total Hours for Day 70 Hr Clock Hours Remaining ROW 1 9/1/2007 8:00 14:00 6:00 6.00 70 - 41.00 70 minues Col E running tally ROW 2 9/2/2007 7:00 15:30 8:30 14.50 or ROW 3 9/3/2007 8:00 15:30 7:30 22.00 29.00 ROW 4 9/4/2007 7:00 15:30 8:30 30.50 ROW 5 9/5/2007 0:00 0:00 0:00 30.50 ROW 6 9/6/2007 0:00 0:00 0:00 30.50 ROW 7 9/7/2007 6:00 14:30 8:30 39.00 ROW 8 9/8/2007 6:00 14:30 8:30 47.50 ROW 9 9/9/2007 7:00 15:30 8:30 47.50 Drops R2 Col E ROW 10 9/10/2007 0:00 0:00 0:00 40.00 Drops R3 Col E ROW 11 9/11/2007 6:00 14:30 8:30 41.00 Drops R4 Col E Col D needs to be in numeric form with 2 decimals and has calculation of Col C - Col B Col E needs to tally Col D, but in 8 day cycles. For this posting, Col D and Col E were added manually to show how it is supposed to look. What I'm looking for is functions that will convert the time values into numbers with 2 decimal places and then, for Col E, to keep a running tally of Col D, but only in 8 day cycles and then in Col F, a calculation to keep track of remaining "time" (70 - latest Col E amount). It's a lot and I'm not sure if it's feasible....but any help would be appreciated. Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Number to Time | Excel Discussion (Misc queries) | |||
Convert number to time | Excel Discussion (Misc queries) | |||
convert number to time | Excel Worksheet Functions | |||
Convert a number to a time | Excel Discussion (Misc queries) | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) |