IF function on calculation of time (calculation of daily subsistence allowances)

[mfrela]

Hi guys

I'm running out of options how to calculate this, so here is the challenge:

I have calculated difference between two dates = 39 h and 20 min

Now I need to calculate costs of hours like this:

WHOLE COSTS:
Full costs: over 12 to 24 hours - hour is 15,00 EUR => costs are?
Half costs: over 8 to 12 hours - hour is 10,00 EUR => costs are?
Lowered costs: over 6 to 8 hours - hour is 5,00 EUR => costs are?

And than I would like to ad "check box" and when is "checked" prices are reduced so REDUCED COSTS table is used (see below).

REDUCED COSTS:
Full costs: over 12 to 24 hours - hour is 12,00 EUR => costs are?
Half costs: over 8 to 12 hours - hour is 8,00 EUR => costs are?
Lowered costs: N/A


So, as you probably guessed, I need this to calculate daily subsistence allowances...

Can you help me out?

Thank you, mfrela

 

[Gerald Higgins]

What happens if it's more than 24 hours, as per your example of 39 hours ?

What happens if it's less than 6 hours ?

What happens if it's less than 8 hours, on reduced costs ?

 

[mfrela]

Dear Gerald

thank you for your answers. I appreciate your interest in my problem and your help!

I hope I explained better now...

1. What happens if it's more than 24 hours, as per your example of 39 hours?

step 1: 39 hours - 24 hours and 21 hours left => 1 unit "Full costs" => 1 x 15 EUR
step 2: 15 hours - 15 hours and 0 hours left => 1 unit "Full costs" => 1 x 15 EUR
So, calculated costs are 30 EUR.

In case we have 43 hours:
step 1: 43 hours - 24 hours and 19 hours left => 1 unit "Full costs" => 1 x 15 EUR
step 2: 19 hours - 12 hours and 7 hours left => 1 unit "Half" cots " => 1 x 10 EUR
step 3: 7 hours - 7 hours and 0 hour left => 1 unit "Lowered" cots " => 1 x 5 EUR
So, calculated costs are 30 EUR.

2. What happens if it's less than 6 hours ?
If less than 6 hours no Costs are calculated.

3. What happens if it's less than 8 hours, on reduced costs ?
If less than 8 hours on REDUCED COSTS no Costs are calculated.

For further questions please, fell free to contact me.

Regards, mfrela

 

[Gerald Higgins]

OK, I'm confused now.

1. What happens if it's more than 24 hours, as per your example of 39 hours?

step 1: 39 hours - 24 hours and 21 hours left => 1 unit "Full costs" => 1 x 15 EUR

Why not 24 hours and 15 hours left ?
24+21=45.
24+15=39.

step 2: 15 hours - 15 hours and 0 hours left => 1 unit "Full costs" => 1 x 15 EUR

Why full costs, not half costs ?


I think I understand the rest . . .

In case we have 43 hours:
step 1: 43 hours - 24 hours and 19 hours left => 1 unit "Full costs" => 1 x 15 EUR
step 2: 19 hours - 12 hours and 7 hours left => 1 unit "Half" cots " => 1 x 10 EUR
step 3: 7 hours - 7 hours and 0 hour left => 1 unit "Lowered" cots " => 1 x 5 EUR
So, calculated costs are 30 EUR.

2. What happens if it's less than 6 hours ?
If less than 6 hours no Costs are calculated.

3. What happens if it's less than 8 hours, on reduced costs ?
If less than 8 hours on REDUCED COSTS no Costs are calculated.

 

[mfrela]

OK, I'm confused now.

Why not 24 hours and 15 hours left ?
24+21=45.
24+15=39.

Sorry, my mistake. To many numbers...
You are right!
step 1: 39 hours - 24 hours and 15 hours left => 1 unit "Full costs" => 1 x 15 EUR
step 2: 15 hours - 15 hours and 0 hours left => 1 unit "Full costs" => 1 x 15 EUR
According to hour limits, 39 hours are basically 2 units of Full costs. So, calculated = 30 EUR.
Why full costs, not half costs?
Because Full Costs are between 12 and 24 hours.


I think I understand the rest . . .Great! Thanks.

 

[Gerald Higgins]

So if 39 hours are basically 2 units of Full Costs, why is 43 hours NOT at least 2 units of Full Costs ?

In post #3 you said 43 hours was 1 unit of full costs, 1 unit of half costs . . . .

 

[mfrela]

You are right: 43 hours are 2 Full costs - but you get the same cost both ways.

I guess it would be more simple so calculate as you said:
In case we have 43 hours:
step 1: 43 hours - 24 hours and 19 hours left => 1 unit "Full costs" => 1 x 15 EUR
step 2: 19 hours - 19 hours and 0 hours left => 1 unit "Full" costs " => 1 x 15 EUR

So, any number which is between 12 and 24 hours is Full cost.
50 hours: 50 - 24 - 24 -2 = 1 full + 1 full + 0
31 hour: 31 - 24 - 7 = 1 full + 1 lowered
89 hours: 24 - 24 -24 - 17 = 4 x full costs

I hope this help (I'm sorry that it is so complicated...)

Thanks for your help!