Count down formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Foss - 28 Aug 2005 18:44 GMT

Hi there,

I need to write a formula that tells me the amount of weeks and days before
the 20th of next month.

Has anyone got such a formula they could pass me? I had a quick look but
it's too hot for my poor little mind to function today!

Any pointers much appreciated..

Cheers,
Foss

Reply to this Message

Sandy Mann - 28 Aug 2005 20:03 GMT

Foss,

If there is a reference date in a cell, (say A1), try:

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>20),20)-A1

If you want it to be independent of any reference cell then try:

=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>20),20)-TODAY()

in each case format the cell as General. If today's date is the 20th then
the formula will show zero

Signature

HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

> Hi there,
>
[quoted text clipped - 9 lines]
> Cheers,
> Foss

Reply to this Message

Foss - 28 Aug 2005 20:41 GMT

Sandy,

Thanks very much, the second one was just what I was after and I've kept
hold of the first as it'll be useful later on!

Cheers,
Foss

> Foss,
>
[quoted text clipped - 22 lines]
> > Cheers,
> > Foss

Reply to this Message

Peo Sjoblom - 28 Aug 2005 20:20 GMT

One way

=DATE(YEAR(TODAY()),MONTH(TODAY())+(DAY(TODAY())>20),20)-TODAY()

Signature

Regards,

Peo Sjoblom

(No private emails please)

> Hi there,
>
[quoted text clipped - 9 lines]
> Cheers,
> Foss

Reply to this Message

Foss - 28 Aug 2005 20:38 GMT

Hi there Peo,

Thanks very much, that's just what I was after!

Cheers,
Foss

> One way
>
[quoted text clipped - 13 lines]
> > Cheers,
> > Foss

Reply to this Message