• 460K Members
• 10.3K Online
• 557K Conversations

New Contributor

# Formula help

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.

# Re: Formula help

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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies