Birthday Calculations

[Ourpat1]

A simple formula, but I sometimes get rogue results.....................is it my logic.....or formatting?..........

{=OR(TEXT(ROW(INDIRECT(F1 &":"& G1)),"MD")=TEXT(E1,"MD"))}

I'm simply trying to get a "True " or "False" in the destination cell to insert a birthday(in E1) that falls between two dates in F1& G1......but now and the I get rogue answers?????

Anyone help?



Pat Savage

[Don]

Hi, welcome to the forum!

Can you include a sample of the data you use? In this case, it might be best to include a sample worksheet that includes output that works and some with the rogue results.

[Sisyphus]

Hi,

Try this one:

Code: Select all

=AND(E1>=F1,E1<=G1)


[Ourpat1]

Hi,

Try this one:

Code: Select all

=AND(E1>=F1,E1>=F1)


The problem is with the year portion of the date, it will always be less than the two "between" dates

[Don]

If you can include a sample of the results, one where it works and one where it doesn't, in an attached spreadsheet, it will be much easier to figure out a working solution for you.

[Sisyphus]

Hi,

My formula has no problem with years but it contained type which I have corrected and apologize for. Here is the corrected version again:

Code: Select all

=AND(E1>=F1,E1<=G1)


Put one date in F1, another date in G1. Write a third date in E1. Put the formula anywhere on the sheet except E1:G1. The cell that has the formula will show TRUE or FALSE depending on whether the date in E1 is between the dates in F1 and G1. Both dates are included in the range. It will work regardless of how far the dates are apart.
Regards,

[Ourpat1]

Her is ome sample data withe rogue error

Pat

[Sisyphus]

Hi,
I can't find the "rogue" part of your data. Here is what I see in row 158:

04.12.07 08.01.12 04.02.12 4 4 2 1 28

Perhaps there is a problem with the formatting or regional settings. You will have noticed that all three dates are fully interchangeable as to their days, months and years. Do you copy any of these data from worksheets not prepared on your current machine? If you are looking at the same data that I see please do explain to me what's wrong.

You may like this formula for your column P:

Code: Select all

=IF(OR(H158="",N158=""),"",N158+O158)


Have a great day!