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

[ephemeruh]

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?

 

[Joe4]

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:

=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:

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:

=LET(dt,DATE(YEAR(TODAY()),MONTH(E2),DAY(E2)),IF(dt>F2,dt-F2,EDATE(dt,12)-F2)) & " days"

 

[ephemeruh]

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:

=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:

=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!

 

[Joe4]

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.