Number of Days Between Two Dates Ignoring Years

sushi514

New Member
Joined
Nov 13, 2020
Messages
29
Hi,

I'm trying to create a dynamic formula that works off of today's date, where I am trying to calculate the number of days between today, and a column of dates that can vary.

E.g. =DATEDIF(G53,TODAY(),"YD") where dates in Column G represents someone's start date with the company, so (5/1/2000, 9/23/2016, 2/1/1990, etc.) - dates vary across the board.

My issue of course is that the formula calculates correctly if the reference date in Column G has already passed today's date (ignoring the year). But does not calculate correctly if the date (ignoring the year) has yet to arrive.

Does anyone have any ideas as to how I can update my DATEDIF formula to account for this, outside of me needing to create two different IF formulas depending on if the month/day in Column G is greater or less than today's current date?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,751
Office Version
  1. 365
Platform
  1. MacOS
outside of me needing to create two different IF formulas depending on if the month/day in Column G is greater or less than today's current date?
I assume this is what you do NOT want, although only 1 IF
=IF(TODAY()>G53,DATEDIF(G5,TODAY(),"YD"),DATEDIF(TODAY(),G53,"YD"))
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
29
Thanks, I mentioned two IFs because I have another IF condition outside of the topic mentioned - apologies!

Unfortunately this formula still doesn't work for me in cases where the date hasn't yet arrived, still seemingly the number of days after the date occurs, until it reaches today's date.

E.g. - the date is 5/31/2010 and it's giving me 326 days between that date and today, 4/22/2021. I'm looking for the result just to give 39 days, without looking at the year. But yet I'm wondering if there's some way I can incorporate this into one longer formula, (rather than splitting out the one column with the entire 5/31/2010 date to ignore the year), to just look at the month and day.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,766
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I left columns C and D since they may be useful.

T202104a.xlsm
ABCDE
1NAME OF PERSONCurrent year -->2021
2JANE31-Jul-0031-Jul-21100100
3ALAN16-Mar-1916-Mar-22328328
Birthday
Cell Formulas
RangeFormula
E1E1=YEAR(TODAY())
C2:C3C2=IF(DATE($E$1,MONTH(B2),DAY(B2))<TODAY(),DATE($E$1+1,MONTH(B2),DAY(B2)),DATE($E$1,MONTH(B2),DAY(B2)))
D2:D3D2=C2-TODAY()
E2:E3E2=IF(DATE($E$1,MONTH(B2),DAY(B2))<TODAY(),DATE($E$1+1,MONTH(B2),DAY(B2)),DATE($E$1,MONTH(B2),DAY(B2)))-TODAY()
 

Watch MrExcel Video

Forum statistics

Threads
1,132,896
Messages
5,655,850
Members
418,247
Latest member
The_aze

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
Top