date difference formatting

[Mattlake]

Hello Excel knowledge base

I hope you can help me, (I did search previous threads but didnt seem to find an answer but it most likely is there)

I have a table with a date in column D and I want to put a formula into the corresponding cell in column H that will show the number of months between column D and today.

is this possible?

I have found I can just conditional formatting for dates that are next month but as it is for a training record I need it to show the next 4 months. My line of thinking was that if i could get the numbers i could then do a conditional formatting with icons.

any help or guidance would be appreciated

Kind Regards
Matthew

 

[Joe4]

Take a look at the DATEDIF function.
See: DATEDIF function - Microsoft Support

 

[Mattlake]

Thank you for the response.

I had looked at the datedif function but i can only get it to show months since the date in column D not how many are to go. (shows 3 if the date is 28/9/23 is in column D and #NUM! if the date is in the future)

as it is a training list i have someone who is due to renew in April 2024 (in cell D17) so i was looking to get a number of months between today and cell D17 to show in cell H17

 

[Joe4]

Just switch the order of the dates in the DATEDIF function.
If you need to check going both ways (past/future), use an IF function to check the dates first, i.e.

=IF(H17>D17,DATEDIF(D17,H17,"m"),DATEDIF(H17,D17,"m"))

 

[Mattlake]

ahh this works so it is showing 9 for a date in November 2024, only thing is that it is also showing 3 for a date in Sept 2023

I have altered the formula to

=IF($H$1>D4,DATEDIF(D4,$H$1,"m"),DATEDIF($H$1,D4,"m"))

(H1 is just todays date) and the records start from row 4, have i done something wrong?

 

[Mattlake]

just a quick one.

I have got this to work =SUM(D87-$H$1)/(365/12) and setting the column to number with 0 decimals

 

[Joe4]

just a quick one.

I have got this to work =SUM(D87-$H$1)/(365/12) and setting the column to number with 0 decimals

SUM serves no purpose in your formula. Get rid of it.
SUM is used to add up a bunch of numbers in a range, i.e.

=SUM(A1:C1)

equals the same thing as

A1+B1+C1

You are not adding up anything in your formula.

 

[Mattlake]

thank you