Date formula Help!

R

RLJ

Active Member
Joined
Mar 15, 2011
Messages
417
Office Version
  1. 365
Platform
  1. Windows
I need some help figuring out a set of formulas to display some dates. In the example below, Column B works since the Work Date is 11/128/2023. Column E does not work since the date is 1/11/2024. I need the dates in column H.

These dates are annual review dates and when in 2023, the dates all work since all is more than 2 months out from the Work Date of 11/18/2023. The dates in Column E do not work because some are less than 2 months out from the work date and some are more than 2 months out from the Work Date of 1/11/2024.

Since the Work Date is 1/11/2024 and 2 months is 3/11/2024, the 60 day value of 3/1/2025 is good, but the 90, 120, 150 & 180 dates are more than 2 months from the Work Date and I need to have the 2024 values vs the 2025 values. Column H is what I would like the formulas to produce.

Basically, I need the formulas to look at the 2 months from Work Date and if they are less, return the next years values and if they are greater, display the current year values.

Sample Date.xlsx
ABCDEFGH
1GoodBadWhat I need
2Work Date11/18/2023Work Date1/11/2024Work Date1/11/2024
3Next Year1/1/2024Next Year1/1/2025Next Year1/1/2025
460 Days from YE3/1/202460 Days from YE3/1/202560 Days from YE3/1/2025
590 Days from YE4/1/202490 Days from YE4/1/202590 Days from YE4/1/2024
6120 Days from YE5/1/2024120 Days from YE5/1/2025120 Days from YE5/1/2024
7150 Days from YE6/1/2024150 Days from YE6/1/2025150 Days from YE6/1/2024
8180 Days from YE7/1/2024180 Days from YE7/1/2025180 Days from YE7/1/2024
9
10
11Workdate + 2 months3/11/2024
Sheet1
Cell Formulas
RangeFormula
H3,E3,B3H3=DATE(YEAR(H2),12,31)+1
H4,E4,B4H4=DATE(YEAR(H3),MONTH(H3)+2,DAY(1))
B5,E5B5=DATE(YEAR(B3),MONTH(B3)+3,DAY(1))
B6,E6B6=DATE(YEAR(B3),MONTH(B3)+4,DAY(1))
B7,E7B7=DATE(YEAR(B3),MONTH(B3)+5,DAY(1))
B8,E8B8=DATE(YEAR(B3),MONTH(B3)+6,DAY(1))
E11E11=DATE(YEAR($E$2),MONTH($E$2)+2,DAY($E$2))


Thank you for your help!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
try this:
This is a corrected version, i failed to use absolute references to the start date:

(Note: I would also caution against interchangably using 60 days = 2 months, 90 day = 30 months, they are not always equivalent)

Book1
ABCDEFGHI
1GoodBadWhat I needdesiredYYYY-MM-DD
2Work Date2023-11-18Work Date2024-01-11Work Date2023-11-182024-01-11
3Next Year2024-01-01Next Year2025-01-01Next Year2024-01-012025-01-01
460 Days from YE2024-03-0160 Days from YE2025-03-0160 Days from YE2024-03-012025-03-01
590 Days from YE2024-04-0190 Days from YE2025-04-0190 Days from YE2024-04-012025-04-01
6120 Days from YE2024-05-01120 Days from YE2025-05-01120 Days from YE2024-05-012025-05-01
7150 Days from YE2024-06-01150 Days from YE2025-06-01150 Days from YE2024-06-012025-06-01
8180 Days from YE2024-07-01180 Days from YE2025-07-01180 Days from YE2024-07-012025-07-01
Sheet5
Cell Formulas
RangeFormula
H3,E3,B3H3=DATE(YEAR(H2),12,31)+1
H4,E4,B4H4=DATE(YEAR(H3),MONTH(H3)+2,DAY(1))
B5,E5B5=DATE(YEAR(B3),MONTH(B3)+3,DAY(1))
B6,E6B6=DATE(YEAR(B3),MONTH(B3)+4,DAY(1))
B7,E7B7=DATE(YEAR(B3),MONTH(B3)+5,DAY(1))
B8,E8B8=DATE(YEAR(B3),MONTH(B3)+6,DAY(1))
I3I3=EOMONTH($I$2,12-MONTH($I$2))+1
I4I4=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),2)+1
I5I5=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),3)+1
I6I6=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),4)+1
I7I7=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),5)+1
I8I8=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),6)+1
 
Upvote 0
I got the same results as your "what I need" column this way. Since you have Office 365, you could probably use the LET function to shorten the the EDATE part of my formulas, but I'm on Office 2007 still, so this is all I could manage.
Book1
ABCDEFGHI
1GoodBadWhat I needCalculated
2Work Date18-Nov-23Work Date11-Jan-24Work Date11-Jan-24
3Next Year1-Jan-24Next Year1-Jan-25Next Year1-Jan-251-Jan-25
460 Days from YE1-Mar-2460 Days from YE1-Mar-2560 Days from YE1-Mar-251-Mar-25
590 Days from YE1-Apr-2490 Days from YE1-Apr-2590 Days from YE1-Apr-241-Apr-24
6120 Days from YE1-May-24120 Days from YE1-May-25120 Days from YE1-May-241-May-24
7150 Days from YE1-Jun-24150 Days from YE1-Jun-25150 Days from YE1-Jun-241-Jun-24
8180 Days from YE1-Jul-24180 Days from YE1-Jul-25180 Days from YE1-Jul-241-Jul-24
9
10
11Workdate + 2 months11-Mar-24
Sheet1
Cell Formulas
RangeFormula
H3,E3,B3H3=DATE(YEAR(H2),12,31)+1
H4,E4,B4H4=DATE(YEAR(H3),MONTH(H3)+2,DAY(1))
B5,E5B5=DATE(YEAR(B3),MONTH(B3)+3,DAY(1))
B6,E6B6=DATE(YEAR(B3),MONTH(B3)+4,DAY(1))
B7,E7B7=DATE(YEAR(B3),MONTH(B3)+5,DAY(1))
B8,E8B8=DATE(YEAR(B3),MONTH(B3)+6,DAY(1))
I3I3=DATE(YEAR(H2),12,31)+1
I4I4=IF(MONTH(EDATE($H$3,2))>MONTH($E$11),DATE(YEAR(EDATE($H$3,2))-1,MONTH(EDATE($H$3,2)),1),EDATE($H$3,2))
I5I5=IF(MONTH(EDATE($H$3,3))>MONTH($E$11),DATE(YEAR(EDATE($H$3,3))-1,MONTH(EDATE($H$3,3)),1),EDATE($H$3,3))
I6I6=IF(MONTH(EDATE($H$3,4))>MONTH($E$11),DATE(YEAR(EDATE($H$3,4))-1,MONTH(EDATE($H$3,4)),1),EDATE($H$3,4))
I7I7=IF(MONTH(EDATE($H$3,5))>MONTH($E$11),DATE(YEAR(EDATE($H$3,5))-1,MONTH(EDATE($H$3,5)),1),EDATE($H$3,5))
I8I8=IF(MONTH(EDATE($H$3,6))>MONTH($E$11),DATE(YEAR(EDATE($H$3,6))-1,MONTH(EDATE($H$3,6)),1),EDATE($H$3,6))
 
Upvote 0
awoohaw said:
try this:
This is a corrected version, i failed to use absolute references to the start date:

(Note: I would also caution against interchangably using 60 days = 2 months, 90 day = 30 months, they are not always equivalent)

Book1
ABCDEFGHI
1GoodBadWhat I needdesiredYYYY-MM-DD
2Work Date2023-11-18Work Date2024-01-11Work Date2023-11-182024-01-11
3Next Year2024-01-01Next Year2025-01-01Next Year2024-01-012025-01-01
460 Days from YE2024-03-0160 Days from YE2025-03-0160 Days from YE2024-03-012025-03-01
590 Days from YE2024-04-0190 Days from YE2025-04-0190 Days from YE2024-04-012025-04-01
6120 Days from YE2024-05-01120 Days from YE2025-05-01120 Days from YE2024-05-012025-05-01
7150 Days from YE2024-06-01150 Days from YE2025-06-01150 Days from YE2024-06-012025-06-01
8180 Days from YE2024-07-01180 Days from YE2025-07-01180 Days from YE2024-07-012025-07-01
Sheet5
Cell Formulas
RangeFormula
H3,E3,B3H3=DATE(YEAR(H2),12,31)+1
H4,E4,B4H4=DATE(YEAR(H3),MONTH(H3)+2,DAY(1))
B5,E5B5=DATE(YEAR(B3),MONTH(B3)+3,DAY(1))
B6,E6B6=DATE(YEAR(B3),MONTH(B3)+4,DAY(1))
B7,E7B7=DATE(YEAR(B3),MONTH(B3)+5,DAY(1))
B8,E8B8=DATE(YEAR(B3),MONTH(B3)+6,DAY(1))
I3I3=EOMONTH($I$2,12-MONTH($I$2))+1
I4I4=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),2)+1
I5I5=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),3)+1
I6I6=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),4)+1
I7I7=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),5)+1
I8I8=EOMONTH(EOMONTH($I$2,12-MONTH($I$2)),6)+1
Click to expand...
For this purpose, we are basing everything on a 30 day month. We don't need the actual calendar days for 60, 90 days etc.
 
Upvote 0
RLJ said:
For this purpose, we are basing everything on a 30 day month. We don't need the actual calendar days for 60, 90 days etc.
Click to expand...
I only added as a caution. Will everyone that sees the report understand this? The forum members are not all in your line of business. I saw the report and had to figure it out.

But, that is beside the point. Does the solution I or @Asbestos_Jen provided answer your question. If not please describe what is wrong.
 
Upvote 0
awoohaw said:
I only added as a caution. Will everyone that sees the report understand this? The forum members are not all in your line of business. I saw the report and had to figure it out.

But, that is beside the point. Does the solution I or @Asbestos_Jen provided answer your question. If not please describe what is wrong.
Click to expand...
@Asbestos_Jen work. Yours still all show 2025 dates, where the 90, 120 etc all still show dates in 2025 and not 2024.
Click to expand...
 
Upvote 0
I didn't do a compare of your desired. I just did: 60, 90, etc. days from the year end from the date provided. And who could guess (if you didn't have the desired listed) that "60 days from YE" is actually 11 months before "90 days from YE". The descriptions in Column G are ambiguous.

But, i'm pleased you found a solution at the forum.

Best wishes!
 
Upvote 0
@Asbestos_Jen I broke the formula yesterday when I was testing it out. I tried a date of 11/18/2023 and the formulas were returning bad results. I know I made it confusing so hopefully I can simplify it here.

On the Work Date a user sets up an event that needs to be reviewed on an annual basis. Regardless of the Work Date, the event review needs to be more than 2 months in the future, so therefore with a work date of 11/18/2023, all the annual review dates are more than 60 days in the future, thus all need to be reviewed in 2024. With the work date of 1/11/2024, only the 60 day from YE is less than 2 months from the work date, thus needing to be pushed to 2025, while all the remaining are greater than 2 months and need a 2024 review date.

I tried working though solutions for the remainder of my day yesterday without having any luck. I think this needs to be based upon only the work date and greater than or less than 2 months taking out the "Next Year" date out of the formula. The below, shows my breaking of the formula and a version that works all using your formulas. I'm hoping you may be able to help me out. I'm hoping I gave a clear need (better than yesterday).

Sample Date Update.xlsx
ABCDE
1BadGood
2Work Date11/18/2023Work Date1/11/2024
3Next Year1/1/2024Next Year1/1/2025
460 Days from YE3/1/202360 Days from YE3/1/2025
590 Days from YE4/1/202390 Days from YE4/1/2024
6120 Days from YE5/1/2023120 Days from YE5/1/2024
7150 Days from YE6/1/2023150 Days from YE6/1/2024
8180 Days from YE7/1/2023180 Days from YE7/1/2024
9
10
11Workdate + 2 months1/18/2024Workdate + 2 months3/11/2024
Sheet1
Cell Formulas
RangeFormula
B3,E3B3=DATE(YEAR(B2),12,31)+1
B4B4=IF(MONTH(EDATE($B$3,2))>MONTH($B$11),DATE(YEAR(EDATE($B$3,2))-1,MONTH(EDATE($B$3,2)),1),DATE(YEAR(EDATE($B$3,2))+1,MONTH(EDATE($B$3,2)),1))
B5B5=IF(MONTH(EDATE($B$3,3))>MONTH($B$11),DATE(YEAR(EDATE($B$3,3))-1,MONTH(EDATE($B$3,3)),1),EDATE($B$3,3))
B6B6=IF(MONTH(EDATE($B$3,4))>MONTH($B$11),DATE(YEAR(EDATE($B$3,4))-1,MONTH(EDATE($B$3,4)),1),EDATE($B$3,4))
B7B7=IF(MONTH(EDATE($B$3,5))>MONTH($B$11),DATE(YEAR(EDATE($B$3,5))-1,MONTH(EDATE($B$3,5)),1),EDATE($B$3,5))
B8B8=IF(MONTH(EDATE($B$3,6))>MONTH($B$11),DATE(YEAR(EDATE($B$3,6))-1,MONTH(EDATE($B$3,6)),1),EDATE($B$3,6))
E4E4=IF(MONTH(EDATE($E$3,2))>MONTH($E$11),DATE(YEAR(EDATE($E$3,2))-1,MONTH(EDATE($E$3,2)),1),EDATE($E$3,2))
E5E5=IF(MONTH(EDATE($E$3,3))>MONTH($E$11),DATE(YEAR(EDATE($E$3,3))-1,MONTH(EDATE($E$3,3)),1),EDATE($E$3,3))
E6E6=IF(MONTH(EDATE($E$3,4))>MONTH($E$11),DATE(YEAR(EDATE($E$3,4))-1,MONTH(EDATE($E$3,4)),1),EDATE($E$3,4))
E7E7=IF(MONTH(EDATE($E$3,5))>MONTH($E$11),DATE(YEAR(EDATE($E$3,5))-1,MONTH(EDATE($E$3,5)),1),EDATE($E$3,5))
E8E8=IF(MONTH(EDATE($E$3,6))>MONTH($E$11),DATE(YEAR(EDATE($E$3,6))-1,MONTH(EDATE($E$3,6)),1),EDATE($E$3,6))
B11,E11B11=DATE(YEAR(B2),MONTH(B2)+2,DAY(B2))


Thank you so much. I do truly appreciate your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Date Calculator
Replies
3
Views
60
Joe4
Joe4
S
  • Question Question
Sales Planners - sync date with days and print in book form
Replies
0
Views
56
ShawnSPS
S
G
  • Question Question
Copying date from one sheet to another based on time left
Replies
2
Views
117
Vogateer
V
J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
365
felixstraube
F
S
  • Solved
Networkdays formula issue
Replies
3
Views
272
Joe4
Joe4

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top