Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! creating code for time to decimal calculations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! creating code for time to decimal calculations
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 -- Regards, Tom Ogilvy "DW" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! creating code for time to decimal calculations | Excel Worksheet Functions | |||
decimal places in calculations | New Users to Excel | |||
Remove Decimal from Calculations | Excel Discussion (Misc queries) | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) | |||
Format a worksheet to carry out all calculations to 2 decimal plac | Excel Discussion (Misc queries) |