Help! creating code for time to decimal calculations

D

DW

Hello,

Sheet snippet:

COL A COL B COL C COL D
COL E COL F
DateTime StrtTime EndTotal 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.50 14.50 or
ROW 3 9/3/2007 8:00 15:30
7.50 22.00 29.00
ROW 4 9/4/2007 7:00 15:30
8.50 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.50 39.00
ROW 8 9/8/2007 6:00 14:30
8.50 47.50
ROW 9 9/9/2007 7:00 15:30
8.50 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.50 41.00 Drops R4 Col E

For this posting, Col D, Col E, and Col F were entered manually to show the
correct values.
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.

What I'm looking for is some code that will convert the time values into
numbers with 2 decimal places (example, 30 seconds would be .5, 45 seconds
would be .75) and then, for Col E, to keep a running tally of Col D, but
only in 8 day cycles (on 9th "day" drop off day 1, 10th "day" drop off day
2, etc.) 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
 
T

Tom Ogilvy

If your time is in H5
=h5*24*60

and format the cell as General should give you your time in minutes.

00:00:30 = 0.5

Look also at the functions Hour, Minute, Second
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top