Forum Discussion

Eyalkam's avatar
Eyalkam
Copper Contributor
Aug 03, 2022
Solved

How to convert time to numbers and calculate different prices

Ok, so I need to report a budget according to time with different price range, and I'm trying to find the right formula- I will really appreciate any help. The specs are: 

Column A: total time of work a day, hours:minutes (for example, 10:45; 13:52; etc.) 

B: I converted the total time to numbers 

Now I need help with the equations: 

Definitions: 

- work day = 9 hours at a daily price of X 

- 2 first extra hours = at a price of (X/9)*125% 

- Anything more than that = (X/9)*150% 

So, I'd like to have:

Column C: if I worked more than 9 hours, I want to have the exact time (hours and minutes) up to 2 full hours, written in hours 

😧 written in numbers 

E: the price (I guess this is easy, ColomD*125%) 

 

Column E: if I worked more that 11 hours, than I want to have the exact time (hours and minutes) of only the 150% hours (not the 2 hours that are worth 125%). 

F: written in numbers 

G: the price 

 

I have tried to play around with "IF THEN" but couldn't get it right. 

Any ideas? 

Resources