Home

Formula help

Nattyg
New Contributor

Please can someone advise on a formula. 

 

Row 1 contains the dates of March, Column A contains name, Column B contains arrival date and Column C contains departure date. 

 

I would like to put the number 3 in every cell from and including the arrival date to the departure date.  This part of the formula I can do, 

 

=IF(AND(O$1>=$B3,O$1<=$C3),3,"")

 

However, I also want to put the number 2 for the day before arrival and 1 for the day after departure.  eg on the attached image row 2 column E should be 2 and column H should be 1 and so on.

 

I have tried various IF, AND OR statements, but I am clearly doing something wrong.

 

1 Reply

Think I've sussed it :-

 

=IF(D$1=$B2-1,2,IF(D$1=$C2+1,1,IF(AND(D$1>=$B2,D$1<=$C2),3,"")))

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
205 Replies