Is there a way to ignore year in calculating duration of days?

ephemeruh

New Member
Joined
Sep 19, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi- For the life of me, I must be thinking about this the wrong way. I am trying to count the # of days between 2 dates, ignoring the year. I have no familiarity with VBA coding, so I'm unsure if that's what it requires. I'm hitting 2 roadblocks.

1) the DAYS function doesn't ignore the year resulting in a large days count because some of my start dates are from years past.

2) tried the DATEDIF function BUT it also messes up. In the DATEDIF, when I place my (start date) as my Issue Date (Column E), the calculation counts until the end of year cycle to Today's date (Column F). If instead, I place Today's date as my (start date) everything is an error because my (start date) is in the future.

How do I look at Today's date (Column F) and simply count how many days until the Issue Date (Column E) without considering the year? The results would essentially only show a value between 0-365 (or 366) days, I think.

I would like to achieve the results in Column K- which displays the "days to" reach the Issue Date from today. Full disclosure, I obtained the Column K values by asking Google how many days until the Issue Date (Column E).

Thank you for any help at all; am I the only one that needs this type of calculation?
 

Attachments

  • DAYSUNTIL.jpg
    DAYSUNTIL.jpg
    209.6 KB · Views: 9

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the Board!

We really cannot ignore the date, as all dates must have a year, or they are not dates at all.
(Note that Excel really stores dates as numbers, specifically the number of days since 1/0/1900. You can see this by entering any valid date and changing the format to "General".
So all dates are in Excel is really numbers with special date formats).


We simply can change all upcoming dates to the current year (for math purposes), or next year for past days (like 9/1).

Try this formula for row 2:
Excel Formula:
=LET(dt,DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)),IF(dt>=F2,dt-F2,EDATE(dt,12)-F2)) & " days"

Here is the proof, based on your example:
1726775266041.png


Note the last example, where Today is the Issue Day. If you would like this to show "365 days" instead of "0 days", simply make one very small edit to the formula like this:
Excel Formula:
=LET(dt,DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)),IF(dt>F2,dt-F2,EDATE(dt,12)-F2)) & " days"
 
Upvote 0
Solution
Welcome to the Board!

We really cannot ignore the date, as all dates must have a year, or they are not dates at all.
(Note that Excel really stores dates as numbers, specifically the number of days since 1/0/1900. You can see this by entering any valid date and changing the format to "General".
So all dates are in Excel is really numbers with special date formats).


We simply can change all upcoming dates to the current year (for math purposes), or next year for past days (like 9/1).

Try this formula for row 2:
Excel Formula:
=LET(dt,DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)),IF(dt>=F2,dt-F2,EDATE(dt,12)-F2)) & " days"

Here is the proof, based on your example:
View attachment 117063

Note the last example, where Today is the Issue Day. If you would like this to show "365 days" instead of "0 days", simply make one very small edit to the formula like this:
Excel Formula:
=LET(dt,DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)),IF(dt>F2,dt-F2,EDATE(dt,12)-F2)) & " days"
Thank you so much for that thorough explanation. I sincerely appreciate the extra details as opposed to just the formula solution. I was able to break down the formula to each piece and make sense of it; I didn’t think to approach it in that manner, such that I “let” my issue dates be current year for the math to work.

Will work on expanding my thinking, thank you so so much!
 
Upvote 0
You are welcome.

Please note: In the future, when marking a post as the solution, please mark the post actually containing the solution, and not your own post acknowledging that another post is the solution.
I have updated this thread for you.
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,569
Members
452,926
Latest member
rows and columns

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