Formula

markster

Well-known Member
Joined
May 23, 2002
Messages
564
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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
 

markster

Well-known Member
Joined
May 23, 2002
Messages
564
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143

ADVERTISEMENT

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
 

markster

Well-known Member
Joined
May 23, 2002
Messages
564
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks Will that's great it works perfectly.
Cheers
Mark
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143

ADVERTISEMENT

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.
 

markster

Well-known Member
Joined
May 23, 2002
Messages
564
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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
 

markster

Well-known Member
Joined
May 23, 2002
Messages
564
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Yes that's great - the months are the most important anyway. Cheers again mate
Mark
 

Forum statistics

Threads
1,144,312
Messages
5,723,649
Members
422,508
Latest member
Lordkit1

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