Forum Discussion
Eyalkam
Aug 03, 2022Copper Contributor
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?
See the attached sample workbook. I didn't calculate the prices, only the times and hours.