help with date formula please
 

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as dd mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

One way:

A4: =DATE(A3,A2,A1)-72

formatted as a date.

In article ,
"Juco" wrote:

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as dd mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

Try this in A4

=Date(A3,A2,A1)-72

If you want it to look exaclty like DD MM YY, use a custom format just like
that. That is, for your custom format use dd mm yy.

Art

"Juco" wrote:

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as dd mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

Write in cell A4:
=DATE(A3,A2,A1-72)

Then goto Format / Cells / Custom and enter dd mm yy

HTH,
Bernd

Bernd,

I didn't know you could do that -- put the subtraction inside DATE.
Interesting.

Art

"Bernd Plumhoff" wrote:

Write in cell A4:
=DATE(A3,A2,A1-72)

Then goto Format / Cells / Custom and enter dd mm yy

HTH,
Bernd

I have put the formula in the cell but get #NUM! What does this mean?


thanks


"JE McGimpsey" wrote in message
...
One way:

A4: =DATE(A3,A2,A1)-72

formatted as a date.

In article ,
"Juco" wrote:

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as dd
mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

Sorry ignore my last post...
It helps if I put a date in the cells!

Silly Juco

"Juco" wrote in message
. ..
I have put the formula in the cell but get #NUM! What does this mean?


thanks


"JE McGimpsey" wrote in message
...
One way:

A4: =DATE(A3,A2,A1)-72

formatted as a date.

In article ,
"Juco" wrote:

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as
dd
mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

I have entered 30 06 2005
and used formula =DATE(A3,A2,A1)-42
The return is 15/10/35?

Is there something else I need to format?

thanks

"JE McGimpsey" wrote in message
...
One way:

A4: =DATE(A3,A2,A1)-72

formatted as a date.

In article ,
"Juco" wrote:

In cell A1 I have date of the month ( eg 14 ) for the 14th day
In cell A2 I have month of the tear (eg 9 or 09) for September
In cell As I have the year 2005
the 3 cells would therefore represent the 14th of September 2005

In cell A4 I want....... the 14th september less 72 days to show as dd
mm
yy (this is where I need help with a formula)

The client fills in cells A1 and A2 so they may put 9 or 09, does this
present a further problem?


thanks

Hmm... when I try that, I get

05/19/2005


In article ,
"Juco" wrote:

I have entered 30 06 2005
and used formula =DATE(A3,A2,A1)-42
The return is 15/10/35?

Is there something else I need to format?