How to: Convert time value to a number

[DW]

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

 

[David Biddulph]

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))1)*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?

 

[DW]

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.

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))1)*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

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