Formula for Per diem (Counting Specific Time and Days).

[marime]

Hi everyone,

I´m new here and I hope someone can help me. English is not my native language but I have seen a question similar to mine that was posted in 2010. So, I'll give it a try, and if I make mistakes please forgive me ray:

I am trying to generate a travel calculating formula for people that works in Production, but I don't know how to make it.

I have the next components:

B7: The day the person starts the journey.
B9: The hour the person starts the journey.
E7: The day the person finishes the journey.
E9: The hour the person finishes the journey.

I need to calculate the number of days between two dates, taking into account the next premises:

1- If the person starts the journey at 6.00 p.m or later and returns the next day at 12 pm or before, the company will give you a full day of per diem.

2- If the person starts the journey at 6.00 p.m or later and returns the next day after 12 pm, the company will give you a full and a half day of per diem.

3- If the person starts the journey before 6.00 p.m and returns the next day at 12 pm or before, the company will give you a full and a half day of per diem.

4- If the person starts the journey before 6.00 p.m and returns the next day after 12 pm, the company will give you two days of per diem.

I hope I'm explaining this ok. Any ideas?

Thanks for looking at this.

Marime.

 

[Special-K99]

There's probably a shorter way but try (and test thoroughly)

=IF(AND(B9>=18,E7=B7+1,E9<=12),1,IF(AND(B9>=18,E7=B7+1,E9>12),1.5,IF(AND(B9<18,E7=B7+1,E9<=12),1.5,IF(AND(B9<18,E7=B7+1,E9>12),2,"outside the range"))))

 

[Indystick]

I've put Start cutoff time in cell D2 and Return Cutoff Time in D3, but otherwise used the cell references you provided. I believe this works in all conditions:

<d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[ code]
<d2,b10<=d3)),1.5,2))[ code]
NOTE: In the scenario you provide, you're assuming that the person returns the next day in every case, so I didn't bother testing on day, simply time.

For some reason the code tag isn't working in this post so here it is:

=IF(AND(B8>=D2,B10<=D3),1,IF(OR(AND(B8>=D2,B10>D3),AND(B8<D2,B10<=D3)),1.5,2))<d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[ code]<d2,b10<="d3)),1.5,2))" [="" code]<="" d2,b10<="d3)),1.5,2))["></d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[></d2,b10<=d3)),1.5,2))[></d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[>

 

[Special-K99]

I've put Start cutoff time in cell D2 and Return Cutoff Time in D3, but otherwise used the cell references you provided. I believe this works in all conditions:

<d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[ code]
<d2,b10<=d3)),1.5,2))[ code]
NOTE: In the scenario you provide, you're assuming that the person returns the next day in every case, so I didn't bother testing on day, simply time.

For some reason the code tag isn't working in this post so here it is:

=IF(AND(B8>=D2,B10<=D3),1,IF(OR(AND(B8>=D2,B10>D3),AND(B8<D2,B10<=D3)),1.5,2))<d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[ code]<d2,b10<="d3)),1.5,2))" [="" code]<="" d2,b10<="d3)),1.5,2))["></d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[></d2,b10<=d3)),1.5,2))[></d2,b10<=d3)),1.5,2))<d2,b10<=d3)),1.5,2))[>

You need to put spaces around any < > or it interprets it as HTML (you'll see your post has been cut off).
There is another way of doing this but I cant recall what it is

 

[Indystick]

=IF(AND(B8 > =D2,B10 < =D3),1,IF(OR(AND(B8 > =D2,B10 > D3),AND(B8 < D2,B10 < =D3)),1.5,2))

Thanks to the tip from Special-K99!

 

[marime]

Thanks a lot to all!!! I'll try them all to see if I can make the one I came up, a little bit shorter:

This is the one I was using:

=+IF(AND(E7>=(TIME(18;0;0));E9<=(TIME(12;0;0)));+B9-B7;IF(AND(E7>=(TIME(18;0;0));E9>(TIME(12;0;0)));+((B9-B7)+0,5);IF(AND(E7<(TIME(18;0;0));E9<=(TIME(12;0;0)));+((B9-B7)+0,5);IF(AND(E7<(TIME(18;0;0));E9>(TIME(12;0;0)));+((B9-B7)+1);"wrong"))))

 

[Special-K99]

In your original post you say B9 and E9 are "the hour" the person starts the journey.
I take that to be an integer from 0 to 23.

But in your formula above you are comparing E9 to a full time not just an hour


You also say E7 is "the day" the person starts the journey.
I take that to be a date, just a date.

But in your formula above and you are comparing E7 to a time
If E7 does contain a date as well your comparison will fail.

To compare a cell with a date AND time to a specific time you need to remove the integer (the date part) so you are comparing time against time,
ie
...IF(AND(E7-INT(E7)<TIME(18;0;0)...

and you don't need all those extra brackets around TIME()

 

[marime]

You are right Special- K99. I was making a mistake taking the date.

But I have two more problems:

- HH.RR. Gave me half of the information. The premises only works when a person travels one day and returns the next one. If a person travels more than 2 days, you count full days.
- Maybe because the formula was wrong. But if I don't complete the dates and hours, the formula still gives me a result.

So.. I came up with this, and it actually works:

E8: Start date.
F8: Start hour.
G8: End date.
H8: End hour.

=IF(OR(ISBLANK($E$8);ISBLANK($F$8);ISBLANK($G$8);ISBLANK($H$8));"";IF(($G$8-$E$8)=1;(IF(AND($F$8>=TIME(18;0;0);$H$8<=TIME(12;0;0));+$G$8-$E$8;IF(AND($F$8>=TIME(18;0;0);$H$8>TIME(12;0;0));+(($G$8-$E$8)+0,5);IF(AND($F$8<TIME(18;0;0);$H$8<=TIME(12;0;0));+(($G$8-$E$8)+0,5);IF(AND($F$8<TIME(18;0;0);$H$8>TIME(12;0;0));+(($G$8-$E$8)+1);"wrong")))));($G$8-$E$8)+1))