Formula

[markster]

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

 

[WillR]

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

 

[The Idea Dude]

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

Cheers

 

[markster]

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]

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]

Thanks Will that's great it works perfectly.
Cheers
Mark

 

[WillR]

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]

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]

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]

Yes that's great - the months are the most important anyway. Cheers again mate
Mark