So I'm re-working an excel spreadsheet and want to set it to where co-workers can enter time as just 1340 (24-hour clock) and have it convert it to 13:40 in the cell. I am then looking to be able to calculate the duration between the two times and have the value calculated to time in decimal. So if between 1500 (15:00) to 1824 (18:24) TASK A is completed, it would be 3.4 hours. The current formula is as follows and the problem is that with the ROUNDUP function, it skews the time to where from 1600-1618 is calculating to a 0.4 whereas it should be a 0.3 (18 minutes...).
=ROUNDUP((((MOD(B63,100)/60+INT(B63/100))/24)-((MOD(A63,100)/60+INT(A63/100))/24)+IF(B63<A63, 1,0))*24,1)
Times should be calculating as such:
1-6 minutes =.1
7-12 minutes =.2
13-18 minutes= .3
19-24 minutes= .4
25-30 minutes= .5
31-36 minutes= .6
37-42 minutes= .7
43-48 minutes= .8
49-54 minutes= .9
55-60 minutes= 1.0
The cell formatting for the times themselves is Codigo Postal. I am hoping to keep this as cell formatting and formulas as I am not familiar with VBA macros, etc. TIA
Bookmarks