Determining If a Date is between Other Dates

Re. my previous comment below, My Excel Toolbox now includes the DateTimeValue function to convert date and/or time text to an Excel date serial number. For example,
=DateTimeValue("01/19/23 1:55 pm")
returns the numeric value 44945.5798611111, which can be formatted as Date (with time) or Time (with date).
Any text recognized as date and/or time will be converted. For example, "Jan 19, 23 13:55" or "19-Jan-23" or "1-19-23" or "1:55:01 pm" etc. If DateTimeValue's argument represents only a time, its result is like Excel's TIMEVALUE function. If DateTimeValue's argument represents only a date, its result is like My Excel Toolbox's DATEVALUE2 function described in my comment at https://excelribbon.tips.net/T007014

Here is an abbreviated version:

Function DateTimeValue(DateTime As Variant) As Date
DateTimeValue = CDbl(CDate(DateTime))
End Function

Notice DateTime can be text like "01/19/23 1:55 pm" or numeric like 44945.5798611111; numeric values are returned directly.
See https://sites.google.com/view/MyExcelToolbox/

@claire
I believe my previous comment was incomplete. The Tip already explains that Excel handles time as the fractional part of a date serial number and discusses conversion of text dates into date serial numbers. I'll try again.
You might be comparing date/time values that are text like "01/19/23 1:55 pm" instead of date serial numbers. The DATEVALUE function converts a text date/time value into an integer date serial number without a fractional time part; therefore, the time part is zero (midnight). The TIMEVALUE function converts a text date/time value into the fractional time part of a date serial number; therefore, the date part is ignored. To convert a text date/time value into a date serial number that includes both the integer date part and the fractional time part, use the following cell function:
=DATEVALUE("01/19/23 1:55 pm")+TIMEVALUE("01/19/23 1:55 pm")
If that date/time text is in cell $A$1, use this cell function:
=DATEVALUE($A$1)+TIMEVALUE($A$1)
For this example, the numeric result is 44945.5798611111. Press Ctrl+1 to format a cell with a serial number like that as Date (with time) or Time (with date).
Be aware that Excel's DATEVALUE function thinks the 21st century ends after 2029, so DATEVALUE("12/07/41") returns 12/07/1941 instead of 12/07/2041. This could be a problem for the current and future decades. For more on this subject, see https://excelribbon.tips.net/T007014_Converting_Coded_Dates_into_Real_Dates.html

@claire
You might be comparing dates that are text like "01/18/23" instead of date serial numbers. Use the DATEVALUE function to convert a text date into a date serial number, then you can compare it with other date serial numbers. Press Ctrl+1 to format a cell containing the serial number as Date or Time.

How do you format a date/time when you need to find if a date/time falls between two date/times? I keep getting FALSE when I can see that the date/time is within the range.

@Matt
If I understand your question, this formula should work (assuming column B is sorted smallest to largest):
==VLOOKUP($A$1,$B:$D,3)
The contents of column C are ignored.

I'm trying to return a value in one column based on the week of the year and today's date. Here's how the table is set up:

A1=TODAY()
Column B is a list of dates for Monday of a given week
Column C is a list of dates for Sunday of the same week
Column D is a list of values

I would like to return the value for D1 if today (A1) is between (or equal to) B1 and C1, D2 if today (A1) is between (or equal to) B2 and C2, etc... I've tried to modify some of the formulas suggested here and come up blank.

Suggestions?

I'm trying to return a value in one column based on the week of the year and today's date. Here's how the table is set up:

A1=TODAY()
Column B is a list of dates for Monday of a given week
Column C is a list of dates for Sunday of the same week
Column D is a list of values

I would like to return the value for D1 if today (A1) is between (or equal to) B1 and C1, D2 if today (A1) is between (or equal to) B2 and C2, etc... I've tried to modify some of the formulas suggested here and come up blank.

Suggestions?

Harold,

I tried this and had to modify it (removed "+1" on the DAY) to get it to exept date from 1 year ago.


=IF(J3="","Inc",IF(J17="","Inc",IF(J3>J17+DATEDIF(J17,DATE(YEAR(J17)+1,MONTH(J17),DAY(J17)),"d"),"Failed","Pass")))

But it does not make a fail if someone accidently puts in a date after todays date.

Thanks for the help, but it has gone to acceptance already.

Hi Fred
A little revision. This will account for a leap year.
Adds 366 days for a non leap year, and 367 for leap year.

=IF(J3="","Inc",IF(J17="","Inc",IF(J3>J17+DATEDIF(J17,DATE(YEAR(J17)+1,MONTH(J17),DAY(J17))+1,"d"),"Failed","Pass")))

Chad
You can make use of VBs ISDATE function as in the following

Function FindDate(ref)
Dim c
For Each c In ref
If IsDate(c) Then
FindDate = c.Address
Exit Function
End If
Next c
End Function

Harold Druss,

It does work, but I had to change the "365" to "366", because it(365) doesn't include todays day exactly 1 year ago as passing.

Thanks for you help.

Hi Fred
Try this, I think it's what you want. I don't know much about Excel.
I tested for empty cells first, then added 365 days to the expiration date.

=IF(J3="","Inc",IF(J17="","Inc",IF(J3>J17+365,"Failed","Pass")))

Allen,

I have been trying to figure this out for a while now, hope you can help.

This is a Pass/Fail for equipment that has a 1 year calibration life.

J3 = Today
J17 = Calibration date
K17 = "P+"/"<F>"/"Inc" (Pass/Fail/Incomplete

a) Formula needs to return "Pass" if J17 is equal to or between J3 and J3 - 1 year.
b) Return an "Inc" if there is no data in either J3 or J17.

This is what I have.

=IF(AND(OR(J17<=J3,J17>=(J3-366)),"P+","<F>"),OR(J17="","Inc"))

Thanks,

Hi,

How to identify the date in the following columns.

C2 = Numbers
D2 = Blank
E2 = Blank
D2 = Date
E2 = Blank
F2 = Numbers

How would you extend this function for the following adaptation.

You are checking a column of values to still see is a value falls between a start and an end date but say the start and end date relates to a policy period i.e. 1st December 2017 to 30th November 2018 and this corresponds with a policy year value of 2017. There could also be other annual periods say 1st December 2018 to 30th November 2019 with a policy year of 2018 and so on. Say these start and end dates and policy year are in a table.

The column of dates that you are checking say relate to a date of an insurance claim and you are wanting to filter this column by adding a formula in the next column to show the appropriate policy year. I'm not sure the AND function works here? Also I'm not sure a VLOOKUP works either nor INDEX and MATCH.

Any ideas?

JD,
Assuming your formula as written has a typo, should have parenthesis after B2 & not comma.
Your current formula will return blank - the final argument in the formula. For your formula to work both parts of the AND function must be satisfied in order to return true to the if statement - in your example the value of C2 is returned if the logical test is true.
If there is no end date in column B then the 2nd half of the AND function is not true & therefore the AND function returns false to the IF function, resulting in the FALSE element of the IF function being returned "".

From your description is seems only the start date is important to check, therefore your formula can be reduced to ;
=IF(D1>=A2,C2,"")

I hope I have interpreted your query correctly.

Hi. Can you please help me. I already have the IF formula where i want to generate a specific value from a column if between two dates.
Example:
Column A1 Start Date, Column B1 End Date, Column C1 Headcount
Data in 2nd row: Start date Jan. 1, 2014 up to Jan.31, 2015, headcount of 7.
FOrmula in Column D2:
=IF(AND(D1>=A2, D1 <= B2, C2, "")

The formula is working fine but the problem is when there is blank or no date in End date column, no value is returned. I would like to return the value in C2 whether there is end date or not. I would like to make use of one formula only that will work whether the End date has data or not.

Your help is very much appreciated. Thanks

Hi Allan,

I am looking to create IF function that does the following.

-If the date is less than the reference date it should mark "Not Due"
-If the date is the same or +3 days the reference date it should mark "Ok"
-If the date is more than 3 days the reference date it should mark "late"
-If it is blank or #N/A it should mark as no update

Thanks I hope you can help me with this one.

Here's a simple solution that doesn't use any date functions:
=IF((A1-D1)*(A2-D1)<0,"Yes","No")

Thanks. Use of median is really something unthought of...Cheers