The cake is not a lie, if we know you deserve it . . .

February 19, 2010 3:13 AM

Excel date puzzle - looking for a simpler solution, ramblings follow

Short version - what's the simplest way, solely within Excel, of checking a cell containing a date (DOB) to see whether it falls on or between dates in 2 other cells (Arrival and Departure Date)?

Long version - I have a spreadsheet which is used to manage bookings and operations info for a small hotel. If we have a guest whose birthday falls during their stay with us we always try to bake them a special cake to serve at afternoon tea. A majority of guests submit their date of birth on booking which is recorded in the spreadsheet.

So - I came up with some conditional formatting a couple of years ago which would highlight the DOB cell if the birthday fell on or between the guest's Arrival Date and Departure Date.

However this ended up being such a hideous mishmash of nested conditions that it was quickly axed from the next version of the sheet in an effort to speed the whole thing up. The problem was writing enough conditions to cover all possible permutations of DAY and MONTH values, i.e. coping with the difference between stays which fall within one single month and stays which run into the following month / year.

Formulae used are linked below (using pastebin due to formatting issues) - these worked for all real-world permutations which occur at the hotel, but I'm pretty sure they wouldn't work as a generalised "does this date fall between these two" tool . . .

A1=DOB, B1=Arrival Date, C1=Departure Date

http://pastebin.com/m466f0d86

posted by protorp to Computers & Internet (12 answers total)

Oh dear, I'm messing this up. Try:

=IF(AND(DATE(YEAR(NOW()),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(NOW()),MONTH(A1),DAY(A1)) <=C1), TRUE, FALSE)


[sorry, if a mod's about, please tidy up my mess]
posted by le morte de bea arthur at 3:48 AM on February 19, 2010

Response by poster: Thank you - that's a big help using DATE to effectively remove the year from the equation that I hadn't thought of to shorten the formula.

However (and this is where theoretical interest takes over from pragmatism!) this leads to situations spanning multiple years not flagging up correctly - eg. DOB 1st January, arrival any date in December, departure any date the following January . . .
posted by protorp at 3:59 AM on February 19, 2010

try something like this for the case when the arrival departure dates are in different years

either

the birthday is the same year, and after the arrival date

or

the birthday is the same and before the departure date

so

=IF(OR(DATE(YEAR(B1),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(C1),MONTH(A1),DAY(A1)) <=C1), TRUE, FALSE)
posted by molecicco at 4:08 AM on February 19, 2010

then combine them both

=IF(

YEAR(B1)=YEAR(C1),

IF(AND(DATE(YEAR(B1),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(C1),MONTH(A1),DAY(A1)) <>
IF(OR(DATE(YEAR(B1),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(C1),MONTH(A1),DAY(A1)) <>

)


and use the same true/false in both cases
posted by molecicco at 4:12 AM on February 19, 2010

Best answer: shit. html problems.

=IF(

YEAR(B1)=YEAR(C1),

IF(AND(DATE(YEAR(B1),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(C1),MONTH(A1),DAY(A1)) less than or equalC1), TRUE,FALSE),

IF(OR(DATE(YEAR(B1),MONTH(A1),DAY(A1)) >=B1, DATE(YEAR(C1),MONTH(A1),DAY(A1)) less than or equal C1),TRUE,FALSE),

)
posted by molecicco at 4:18 AM on February 19, 2010

Best answer: Another way you might tackle this is by taking the difference between the birthday and the arrival date, modulus 365.25, and seeing whether it is less than the difference between the arrival and departure dates. It'll get a little wonky because of leap years, but adding a couple days margin will ensure you see them, while getting the occasional false positive.

The formula'd be (for line 2):

=MOD(A2-B2,365.25)<(C2-B2+1)
posted by FuManchu at 4:44 AM on February 19, 2010

=AND(DATE(YEAR(MAX(B1,A1)),MONTH(A1),DAY(A1))>=B1,DATE(YEAR(MIN(C1,A1)),MONTH(A1),DAY(A1))<=C1)

Seems to work ok. This is the sample data i tested (dd/mm/yyyy):

3/03/2010	1/01/2010	4/04/2010	TRUE
15/11/2010	21/10/2010	14/11/2010	FALSE
2/02/2010	12/12/2009	2/02/2010	TRUE
3/04/2010	3/04/2010	6/08/2010	TRUE
12/06/2010	16/07/2010	1/08/2011	FALSE
6/06/2009	30/05/2008	14/01/2010	TRUE

posted by snoopie at 4:48 AM on February 19, 2010

Seems like my DOBs are a bit unrealistic :)

=AND(DATE(YEAR(MAX(NOW(),B1)),MONTH(A1),DAY(A1))>=B1,DATE(YEAR(MIN(C1,B1)),MONTH(A1),DAY(A1))<>
3/03/1953 1/01/2010 4/04/2010 TRUE
15/11/1948 21/10/2010 14/11/2010 FALSE
2/02/1984 12/12/2009 2/02/2010 TRUE
3/04/1974 3/04/2010 6/08/2010 TRUE
12/06/1997 16/07/2010 1/08/2011 FALSE
6/06/1969 30/05/2008 14/01/2010 TRUE
29/02/1904 1/02/2004 1/03/2004 TRUE

posted by snoopie at 5:00 AM on February 19, 2010

Best answer: Ok kinda stuffed up that formatting.

=AND(DATE(YEAR(MAX(NOW(),B1)),MONTH(A1),DAY(A1))>=B1,DATE(YEAR(MIN(C1,B1)),MONTH(A1),DAY(A1))<=C1)

3/03/1953	1/01/2010	4/04/2010	TRUE
15/11/1948	21/10/2010	14/11/2010	FALSE
2/02/1984	12/12/2009	2/02/2010	TRUE
3/04/1974	3/04/2010	6/08/2010	TRUE
12/06/1997	16/07/2010	1/08/2011	FALSE
6/06/1969	30/05/2008	14/01/2010	TRUE
29/02/1904	1/02/2004	1/03/2004	TRUE

posted by snoopie at 5:04 AM on February 19, 2010

Response by poster: Thanks one and all for taking the time to answer, despite the somewhat befuddled phrasing of the question. Seeing several different approaches to solve the problem was exactly what I was looking for!
posted by protorp at 7:08 AM on February 19, 2010

so now that that's all worked out, where is this cake baking hotel?
posted by bDiddy at 9:00 AM on February 19, 2010

I don't have formula suggestions, but it occurs to me that the year doesn't matter, unless you're wanting to calculate their age. So you should be able to toss out any part of the formula that has to do with year.
posted by wwartorff at 6:17 PM on February 19, 2010