Date Functionality

[aaron.kempf]

So i want to add dates in Excel

i want to say.. this is this month; next month is

I would normally use DateAdd function.. or dateserial-- or something..

but these aren't working for me in Excel; it just gives me this
annoying '#ERROR' bullshit

i mean-- aren't these standard functions?

 

[Harlan Grove]

(e-mail address removed) wrote...

So i want to add dates in Excel

i want to say.. this is this month; next month is

I would normally use DateAdd function.. or dateserial-- or something..

but these aren't working for me in Excel; it just gives me this
annoying '#ERROR' bullshit

i mean-- aren't these standard functions?

When all else fails, consider reading online help. Enter 'date' (w/o
the quotes) in box 1 of the help index, and the 3rd entry in box 2
should be 'Date worksheet function'. The last 2 paragraphs in the
Syntax section should make it clear that this is the function you'd use
to find the next month [unless you mean something like
1+MOD(MONTH(x),12)].

 

[aaron.kempf]

ok well that 'Date' worksheet function is uh.. known as DateSerial in
the VB world.

I want to add a month to it.
I want to add a day to it.
I want to add a year to it.

I dont see how this Date function can help me.

Like.. uh.. how do i get to the last day of the month.. i want to add a
month and then subtract a day from 4/1 in order to get 4/30.

I just think that these functions should be standardized.. across
EVERYTHING.
SQL SERVER doesnt have a 'DATE' function-- it has a

and in VB DATE() is a totally different function.

If nothing else there should be help in Excel when you lookup 'DateAdd'
it should say 'sorry excel is too retarded to have these standard
functions'.

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

ok well that 'Date' worksheet function is uh.. known as DateSerial in
the VB world.

There's lots different between VB and Excel worksheet functions.

I want to add a month to it.

Simplistically,

=DATE(YEAR(x),MONTH(x)+1,DAY(x))

but if x were 31-May-2005, this would return 1-Jul-2005. If you want
30-Jun-2005 instead, it gets more complicated.

=DATE(YEAR(x),MONTH(x)+1,MIN(DAY(x),DAY(DATE(YEAR(x),MONTH(x)+2,0))))

or

=x+31-DAY(x+31)+(MONTH(x+31)-MONTH(x)=1)*DAY(x)

or if you load the Analysis ToolPak

=EDATE(x,1)

I want to add a day to it.
=x+1

I want to add a year to it.

Either

=DATE(YEAR(x)+1,MONTH(x),DAY(x))

which only screws up if x were 29-Feb in a leap year, or

I just think that these functions should be standardized.. across
EVERYTHING.

Fine, but what criteria would be used to establish 'THE ONE TRUE WAY!'?

First language to provide the feature? Then we'd probably be stuck with
the COBOL or PL/I function.

Democratically by number of users using an existing common feature
named differently in different systems? Then get used to the Excel way
of doing things.

Aaron's preference? Become world dictator, then you can impose your
will. Until then, hold your breath.

SQL SERVER doesnt have a 'DATE' function-- it has a

A . . . what?

and in VB DATE() is a totally different function.

VB's Date is an OLD, OLD function from the original Dartmouth Time
Sharing System BASIC that Microsoft, in it's infinite marketing wisdom,
decided not to redefine in its BASIC dialects.

Excel's DATE function comes from 123's @DATE. No prizes for guessing
why.

If nothing else there should be help in Excel when you lookup 'DateAdd'
it should say 'sorry excel is too retarded to have these standard
functions'.

No, it should say, "YOU'RE NOT USING SQL, MORON!, YOU'RE USING EXCEL."