Date Formula Required

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

Hi,

I need to be able to subtract a month & 7 days from a
given date. I know that I can do this with DateAdd() in
VBA. But is there a worksheet function alternative ???

While Iam able to use Datediff() in my excel sheet, why am
I not able to use DateAdd() in my excel sheet ???


Thanks a lot

Joseph
 
Joseph,

Is this what you want

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)-7)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
While Iam able to use Datediff() in my excel sheet, why am
I not able to use DateAdd() in my excel sheet ???
Click to expand...

You cannot use the VBA Datediff() function in your excel sheet.

The worksheet function DATEDIF() is not the same function. It is spelled
differently and has different arguments.

See other responses for the answer to your other questions.


--ron
 
Hi Ron,

Thanks a lot for pointing out the diff in Datediff() and
Datedif(). But you've managed to raise a few more doubts
in my mind. Im sorry if Im being too dumb for not having
known the answers for the following qns:

1) Why cant I find the datedif() function in the
Insert>Function>Function Categories>Function Names list ?

2) How many such "hidden functions" are there in Excel and
how can I know them ??


Pls do help me out on this, Thanks once again for putting
up with me :-)

Regds,
Joseph
 
Hi Ron,

Thanks a lot for pointing out the diff in Datediff() and
Datedif(). But you've managed to raise a few more doubts
in my mind. Im sorry if Im being too dumb for not having
known the answers for the following qns:

1) Why cant I find the datedif() function in the
Insert>Function>Function Categories>Function Names list ?

2) How many such "hidden functions" are there in Excel and
how can I know them ??


Pls do help me out on this, Thanks once again for putting
up with me :-)

Regds,
Joseph
Click to expand...

We're here to try to help folk with questions. (Sometimes we succeed <g>).

In any event the DATEDIF() worksheet function is the only "hidden" one that I
know of. It was documented in HELP in one version of Excel, perhaps 2000, but
it is not documented in any of the others.

Chip Pearson, a regular contributor, has a web site on which the function is
documented: http://www.cpearson.com/excel/datedif.htm


--ron
 
Back
Top