date difference formatting

Mattlake

Board Regular
Joined
Apr 9, 2020
Messages
91
Office Version
  1. 2021
Platform
  1. Windows
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
 

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.
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
 
Upvote 0
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.
Excel Formula:
=IF(H17>D17,DATEDIF(D17,H17,"m"),DATEDIF(H17,D17,"m"))
 
Upvote 0
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
Excel Formula:
=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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
Excel Formula:
=SUM(A1:C1)
equals the same thing as
Excel Formula:
A1+B1+C1

You are not adding up anything in your formula.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,880
Messages
6,175,152
Members
452,615
Latest member
bogeys2birdies

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