Date formula not working as intended

S

suciulaurentiu

New Member
Joined
Oct 20, 2014
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have the following question regarding the formula and what I am trying to achieve:

Start_Date , End_date
months_interval = one number of random months (ex. 48)

Start_Date - Today (ex. 01.11.2021)
End_date - =IFERROR(EDATE((EDATE(Start_date;months_interval))-1;1);""))

I am trying here to calculate the end date by adding some months interval and the day should -1 day of the result ( we should have to count even for the february months, if it's a leap year or not...)

Ex: Start : 01.12.2021 End: 31.12.2023
Start: 05.12.2021 End: 04.01.2024

Right now my end result is: Start : 01.12.2021 End: 30.12.2023 not sure what is wrong..
 
Sort by date Sort by votes
RoryA said:
If you just want the end of the relevant month, why can't you just use EOMONTH and not subtract 1?
Click to expand...
That's because I don't want the end of the month..I calculate it all, reporting to ()TODAY . Today being 31.01.2022 is just a coincidence, but i might have all days of the month for a start date from 01 till 31
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
For ex:

If today were 01.02.2022, than after 36 months end date is 28.02.2025

05.02.20221204.03.20232004.11.20233104.10.20244104.08.2025
1304.04.20232104.12.20233204.11.20244204.09.2025
1404.05.20232204.01.20243304.12.20244304.10.2025
1504.06.20232304.02.20243404.01.20254404.11.2025
1604.07.20232404.03.20243504.02.20254504.12.2025
1704.08.20232504.04.20243604.03.20254604.01.2026
1804.09.20232604.05.20243704.04.20254704.02.2026
1904.10.20232704.06.20243804.05.20254804.03.2026
2804.07.20243904.06.20254904.04.2026
2904.08.20244004.07.20255004.05.2026
3004.09.2024
5104.06.20266104.04.20277104.02.20288104.12.2028
5204.07.20266204.05.20277204.03.20288204.01.2029
5304.08.20266304.06.20277304.04.20288304.02.2029
5404.09.20266404.07.20277404.05.20288404.03.2029
5504.10.20266504.08.20277504.06.2028
5604.11.20266604.09.20277604.07.2028
5704.12.20266704.10.20277704.08.2028
5804.01.20276804.11.20277804.09.2028
5904.02.20276904.12.20277904.10.2028
6004.03.20277004.01.20288004.11.2028
 
Upvote 0
So the 29th, 30th and 31st of Jan 2022 should all return 28th Feb 2025 when adding 37 months?
 
Upvote 0
RoryA said:
So the 29th, 30th and 31st of Jan 2022 should all return 28th Feb 2025 when adding 37 months?
Click to expand...
29.01.2022, after 37 months, should end in : 28.02.2024, 25, 26, 27, etc....

30.01.2022 ends in bold column:

29.01.202429.02.2024
29.01.202501.03.2025
29.01.202601.03.2026
29.01.202701.03.2027
29.01.202829.02.2028
29.01.202901.03.2029

And 31.01.2022 ends, which is wrong:

30.01.202401.03.2024
30.01.202502.03.2025
30.01.202602.03.2026
30.01.202702.03.2027
30.01.202801.03.2028
30.01.202902.03.2029
 
Upvote 0
From all my test, I`ve noticed that the only error in calculations are specific in today 31.01.2022... which is driving me crazy :ROFLMAO:
 
Upvote 0
What are the dates in the first columns?
 
Upvote 0
I don't understand your logic then. If the dates for 30/1 are correct, why are the dates for 31/1 incorrect, when they all appear to be one day later than the correct ones for 30/1?
 
Upvote 0
Hi,
So for a better understanding:

TodayIncorrectCorrect ( -1 )
31.01.202224months01.03.202429.02.2024
36months02.03.202501.03.2025
48months02.03.202601.03.2026
60months02.03.202701.03.2027
72months01.03.202829.02.2028
84months02.03.202901.03.2029
 
Upvote 0
You didn't explain how 30/1 data can be correct if the 31/1 results (which are all one day later) are wrong.

I can't see how any of the dates you just posted make sense for the intervals stated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Cashflow forecast based on start dates and either monthly, bi-monthly, quarterly, annually
Replies
2
Views
784
felixstraube
F
D
  • Question Question
Generating a list of Dates using nested array inside SEQUENCE function?
Replies
2
Views
257
Cubist
Cubist
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
I
  • Question Question
Accrual Worksheet Next Pay date Formula
Replies
0
Views
266
ItalianPlatinum
I
C
  • Solved
Need help w/ formula to calculate next X-month increment from a date based on today's date
Replies
3
Views
961
ChrisLaR
C

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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