Formula

markster

Well-known Member
Joined
May 23, 2002
Messages
579
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hope this makes sense! I work for an organisation which gives grants. We always label awards with the date that they were agreed at committee i.e 1st February 2002. I want to set up a column where it automatically works out the months/days that have elapsed since the award was made but linked to the current days date so whenever I open it it always works out from that days date of award. Any help anyone could give me would be much appreciated.
Cheers
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You could use the following

=TODAY()-A1

(assuming A1 contains the date of award)
Set your cell format to NUMBER and will show number of days since award.

HTH
 
Upvote 0
Hi Markster,
Try =now()-THE CELL THAT CONTAINS THE GRANT DATE

Cheers
 
Upvote 0
Thanks guys - one more thing - I have no problem in getting it to work out the days - anyone know of the best formula for converting number of days to month/days ???? I've tried a few but it just doesn't work.
Cheers once again.
 
Upvote 0
Mark...

Here's one way - I don't guarantee it's the best. But here goes.

If A2 has start date (i.e. 1/1/2002)
B2 = No. of months (formula is =+MONTH(TODAY())-MONTH(A2)
C2 = No.of days (formula is =TODAY()-EDATE(A2,B2)

So you have B2 is Months = 8 (since Jan)
C2 is Days = 8 (i.e. it's 9th Sept)

?? OK
 
Upvote 0
Thanks Will that's great it works perfectly.
Cheers
Mark
 
Upvote 0
Ah... bit of an issue though. If you put a date like 31 jan in it will give you 8 months and -21 days.... eek.

You probably dont want that so here's a workaround.

For the C2 formula use the following formula

=IF((TODAY()-EOMONTH(A2,B2))<0,(TODAY()-EOMONTH(A2,B2-1)),(TODAY()-EOMONTH(A2,B2)))

This will return the following result for 31 Jan

8 Months & 9 Days.....

Now, all this will be fine until the months goes above 12.... If you have values > 1 year, you may want to build in a No. of years function too....

So you'd get Years, Months, Days

But if its all under 12 months you're OK with what you've got there.
 
Upvote 0
Will actually we could have some that go over 12 months didn't think of that. WIll the months just notch up i'e 13,14 15 or will it just **** up?
 
Upvote 0
How about trying this.

Cell B2 - formula =12*(YEARFRAC(A7,TODAY(),3))

Now this will give you a decimal fraction...i.e. if cell is number format - a date of 1/1/2001 will give you a result of 20.25 months. Would this suffice?

Certainly cuts out the issue of month value being > 12 and far simpler
 
Upvote 0
Yes that's great - the months are the most important anyway. Cheers again mate
Mark
 
Upvote 0

Forum statistics

Threads
1,218,899
Messages
6,145,095
Members
450,590
Latest member
Naneng

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