SOLVED

How to convert time to numbers and calculate different prices

New Contributor

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? 

2 Replies
best response confirmed by Eyalkam (New Contributor)
Solution

@Eyalkam 

See the attached sample workbook. I didn't calculate the prices, only the times and hours.

 

S1638.png

Super !!!
THANKS for the help (and for responding FAST)