365 day countdown between two dates

Hi ppl,

I am in great need of some help! I am trying to achieve the following:

I have been asked by work to create a document which effectively counts down the number of days since a pre defined day, for example:

If

Start date = 22/11/1980
Todays date = 27/10/10

I need a way which it can count down the day from today's date till 21/11/10. However I cant change the start date year to make the sum easier

so for example, the answer would be -26, as todays date increased the number of days increases, until there is 0 days left. Then its resets itself and starts counting down from -365 days

I have tried many formula's such as "=SUM(E15-B15)-(365* no of years)" however this forumla doesnt seem to work.

Can anyone help me?

Many thanks!

Tom

hey Tom - i would quickly check the forum rules and re-post with a more accurate thread tittle before you are red carded.....

Would that be the 21st day of the 14th month of the year, or the 14th day of the 21st month? :-)

Try formatting the cell as general AFTER you have entered the formula

Regards


@Darren: good call

With:

Please Login or Register  to view this content.

Or, you can miss out the intermediate step (F16):

Please Login or Register  to view this content.

The output cell, F20, can be any cell you choose.

If you can get the basic calculation and display to work, you can substitute alternative values.

You might need to add a fudge factor if you're looking to "adjust" the result (for whatever reason)

Regards

thankyou TMShucks, thankyou TMShucks, however that only works for dates approaching the E15 date. If you change the B15 value to 28/10/09 the output is -1 however I would like it to say -364 if you understand me?

Many thanks,

Tom

Tom

Not sure why your last example should be -364

=ROUND(MOD(B9-B8,365.2425),0)-365

as far as I can see your logic is

how long is it to the next occourence of DD/MM

Since this should always be in the future the range should be 0-365(366) negated if you wish to have a negative figure.

B8 is the start date
B9 is the days until

this might help :-
=DATE(YEAR(D9)+(DATE(YEAR(D9),MONTH(D8),DAY(D8))<D9),MONTH(D8),DAY(D8))

Will calculate the next aniversary

=DATE(YEAR(D9)+(DATE(YEAR(D9),MONTH(D8),DAY(D8))<D9),MONTH(D8),DAY(D8))-D9

Will calculate how many days until this date

select the cell you want yo highlight, use conditional formatting from the format menu

in the first box, change the dropdown to formula

then simply type =$f$12=$g$12, select the formatting style you require, and you should highlight a week before!

Originally Posted by tom_k88

I need to sort the date by the closest date to todays date being at the top of the table.

Hello Tom,

What do you mean by closest, do you mean the one that's nearest to an anniversary as related to your original question?

For your original question, assuming you have a date in A1 then this formula will give you a countdown to the next anniversary as you requested

=B$1-EDATE(A1,DATEDIF(A1,B$1-1,"y")*12+12)

where B1 = TODAY()

That uses EDATE which requires Analysis ToolPak to be enabled (or you can use a longer version without EDATE).

You could use that formula against each date and then sort by that column

Originally Posted by tom_k88

1.) I thought I had made a formula to work out the week before the anniversary but when I tested it, it didnt work correctly. What formula do I require?

If the date in A1 is 22/11/1980 then I assume that right now you want that to show 15/11/2010......but when does that change to 15/11/2011, on 15/11, after 15/11....or on or after 22/11?

You can adjust my previous suggestion to do any of those, e.g.

=EDATE(A1,DATEDIF(A1,B$1+6,"y")*12+12)-7

that will display 15/11/2010 until 15/11/2010...then after that it will display 15/11/2011

I put today's date in A7 so you only have to reference a single cell

In D12 copied down I used this formula for the next anniversary date

=EDATE(C12,DATEDIF(C12,A$7-1,"y")*12+12)

and then in C12 copied down I used

=IF(D12=A$7, "Ring Client",IF(D12-A$7<=7,"Within 7 days","Pending"))

That will change the text accordingly - I changed conditional formatting so the "Within 7 days" rows are orange.

For today's date nothing was formatted so I changed A7 to = TODAY()+19 - that's just for testing you should change it back to just = TODAY()

You can sort by the Anniversary date or the "days to Anniv" column. Either way you need to do that as the date changes. Does that work for you?

If EDATE formula doesn't work try enabling Analysis ToolPak

Tools > add-ins > tick "Analysis ToolPak"

see attached

thankyou daddylonglegs, I cant thank you enough! Just a small issue, there seems to be some issues with Anniversary collumn. Some of the dates are not just a year in advance for example, row 26, the start date is 12/02/08 and the anniversary is 12/02/11?

When I next paid, I will donate something towards the site, your help has been greatly appreciated!



Many thanks,

Tom