IF formula for a date range

Solved/Closed
luxe - Jan 13, 2021 at 01:34 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jan 18, 2021 at 11:57 AM
I have two columns, booked date and las rev date for 2021
If the dates in these two columns are between Jan 1 - 24 then it would be a jan report date
If the dates are between Jan 25 - Feb 24, then a feb date, etc.
I then need to do this same thing for 2020.
What would the formula look like? so far I have (was trying to build it off another formula but am having issues

=IF(ISBLANK([Booked Date]@row), 0, IF(ISBLANK([Last Rev. Date]@row), IF(AND([CHECK FOR COMPLETED]@row = 0, TODAY() > 24, 0), IF(AND([CHECK FOR COMPLETED]@row = 0, TODAY() > [ACTUAL - Finish]@row), 1, 0))

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Jan 14, 2021 at 12:00 PM
Hi Luxe,

Not sure what you mean by "jan report date". Do you mean an actual date or the text?

Can you show some sample data for better understanding?

As I understand it now, you have for example 4-1-2021 in A2 and 20-1-2021 in B2 (d-m-yyyy). In this case want a "jan report date" as a result.

=TEXT(B2,"mmm") & " report date"

But this can't be what you are looking for, right?

Best regards,
Trowa

Hi,
I have a date range. The initial booked date and the the last revised date. I need a formula that reviews these two columns and the date range given up above so that I can determine which month the dates are in. This is for a report for accounting.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 18, 2021 at 11:57 AM
Hi Luxe,

So you want to retrieve the lowest and highest month used in a range.

For low:
=PROPER(TEXT(MIN(\$A\$1:\$B\$3),"mmmm"))

For high:
=PROPER(TEXT(MAX(\$A\$1:\$B\$3),"mmmm"))

Best regards,
Trowa