SOLVED

New 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?

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

Re: How to convert time to numbers and calculate different prices

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

Re: How to convert time to numbers and calculate different prices

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