days ranges apply fee

[mick001]

Hello,

Can you help me with the following.

In excel I need to calculate how long somebody stays and a apply a different fee.

between 1 and 7 nights 1,5 euro
between 8 and 14 nights 2,1euro
longer then 15 nights longer no extra charge.

do you have an idea? please no vsb..


thanks alot

 

[Nine Zero]

assuming your dates are in A1 and B1 then in C1 put

=IF(AND((B1-A1)>0,(B1-A1)<8),1.5,IF(AND((B1-A1)>7,(B1-A1)<15),2.1,0))

 

[mick001]

assuming your dates are in A1 and B1 then in C1 put

=IF(AND((B1-A1)>0,(B1-A1)<8),1.5,IF(AND((B1-A1)>7,(B1-A1)<15),2.1,0))

Thanks for your help but the formula doesn't seems to work. excel doesn't accept it.

the dates are in B5 and C5, I changed that but still it doens't acept it.
I made a mistake when it is longer than 14 nights the fee is 1,1

thanks for your help!!

 

[Nine Zero]

Assuming your dates are in B5 and C5

=IF(AND((C5-B5)>0,(C5-B5)<8),1.5,IF(AND((C5-B5)>7,(C5-B5)<15),2.1,IF((C5-B5)>14,1.1,0)))

tested and works

 

[mick001]

Assuming your dates are in B5 and C5

=IF(AND((C5-B5)>0,(C5-B5)<8),1.5,IF(AND((C5-B5)>7,(C5-B5)<15),2.1,IF((C5-B5)>14,1.1,0)))

tested and works

thanks for your help, I still get this error message:

 

[mick001]

 

[Joe4]

You cannot paste images like that.

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.