I have a spreadsheet that shows individuals arriving on certain days & departing on other days. For instance, arriving on 6/15/09 and departing on 6/20/09. Then I have a column per day (heading is the day, 6/15/09, 6/16/09, 6/17/09 etc). I want to fill in the day by day columns with a one on EACH day that the individual is in the hotel - so, ideally, if the arrival date is 6/15/09, then under the column labeled 6/15/09, a "1" would be placed. If their arrival date is 6/16/09, then the column labeled 6/15/09 for that person would remain empty or get a "0". Can someone help me with this formula? I tried the IF formula, but it only appears to work for text?

re: I tried the IF formula, but it only appears to work for text?Definitely not so! IF's can be used for a variety of mathematical criteria.

e.g. =IF(SUM(A1,B1)>14,SUM(C1:C5),B1*8)

Try this for your question:

Put your Date List in Row 1, starting in Column C

Put your Arrival Date in A2

Put your Departure Date in B2Put this formula in C2 and drag it along under your Date List:

=IF(OR(C1<$A$2,C1>$B$2),0,1)

Hi, Make sure that you are entering dates that Excel recognizes.

When Excel recognizes an entry as a date it converts it to a number, and formats it as a date.

Go to a cell with a date in it and then try formating the cell with different date formats. If the cells change when you change the format, Excel recognized them as dates. If there is no change, then Excel has treated the entry as text only.

If text only, re enter the dates as 09-Oct-2009 for example.

Also, once a date is entered in Excel, such as 09-Oct-2009 in Cell A1:

In cell B1, put =A1+1 and B1 will show 10-Oct-2009.This makes it easy to create a list of dates, and calculations become easier - you can subtract one date from another to get number of days, for example.

Once Excel has recognized a date, it will always store it as a number, but you can format it anyway you like, even get it to show the day of the week.

09-October-2009 is stored as 40095, and 10-Oct is 40096.

(Time is stored in the decimal part of the number, 40095.5 is half way through 09-October, i.e. 12 noon).

HTH

Regards

Thanks! with some adjustments, the formula worked for me - I adjusted it as follows:

=IF(AND(C$1>=$A2,C$1<$B2),1,0)6/14/2009 6/15/2009 6/16/2009 6/17/2009

6/15/2009 6/20/2009 0 1 1 1

6/14/2009 6/21/2009 1 1 1 1

Glad to have helped.

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History