# Formula

#### markster

##### Well-known Member
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

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

Cheers

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.

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

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

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.

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?

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

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

Replies
5
Views
438
Replies
11
Views
405
Replies
5
Views
242
Replies
18
Views
633
Replies
11
Views
823

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.

### Which adblocker are you using?

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

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