date calculation

[afdmello]

In one column cell A1 I have date (eg Feb -1-2009) in another(B1) I have
25( which represents number of days).How do get the date in the third column
cell (C1) which will be equal to A1+B1. The answer should be in date (eg
Feb-26-2009).

Thanking you

Afd

 

[joeu2004]

In one column cell A1 I have date (eg Feb -1-2009) in another(B1)
I have 25( which represents number of days).How do get the date
in the third column cell (C1) which will be equal to A1+B1. The
answer should be in date (eg Feb-26-2009).

=A1+B1

Format as Date, if necessary.

 

[Steve]

In one column cell A1 I have date (eg Feb -1-2009) in another(B1) I have
25( which represents number of days).How do get the date in the third
column cell (C1) which will be equal to A1+B1. The answer should be in
date (eg Feb-26-2009).

Thanking you

Afd

I just entered your example above in Excel 2003 and got the correct result
by entering "=A1 + B1".

Hope this helps!

--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
Unemployed Car Guy - Trying To Earn A Living
35 Years of G.M. Parts Experience
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)

 

[afdmello]

Jou

I am not getting the same result as you. I am getting #VALUE!
in the cell even though i format it as date

afd

In one column cell A1 I have date (eg Feb -1-2009) in another(B1)
I have 25( which represents number of days).How do get the date
in the third column cell (C1) which will be equal to A1+B1. The
answer should be in date (eg Feb-26-2009).

=A1+B1

Format as Date, if necessary.

 

[joeu2004]

I am not getting the same result as you. I am getting
#VALUE! in the cell even though i format it as date

My guess is that you entered the dates exactly as you describe in your
original posting, so they are text, not "serial numbers".

Ideally, you should change the form in which you enter the date; enter
it as 2/1/2009, for example. Then you can use a Custom format to make
the cell appear the way you want (well, almost).

Caveat: With the Custom format MMM-DD-YYYY, you will see
Feb-02-2009. If the leading zero in the day is not acceptable, post
back for assistance. There may be a Custom format that I'm not aware
of.

Alternatively, you have to do a lot more work to do the computation.
For example:

=B1 +
date(mid(A1,8,4),
int((find(mid
(A1,1,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3)+1,
mid(A1,5,2))

Format as Custom MMM-DD-YYYY, with the caveat again that days less
than 10 will have a leading zero.


----- original posting -----

 

[joeu2004]

int((find(mid(A1,1,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3)+1,

Geesh! The following is much more straight-forward:

match(mid(A1,1,3),

{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"})

 

[Ron Rosenfeld]

My guess is that you entered the dates exactly as you describe in your
original posting, so they are text, not "serial numbers".

Ideally, you should change the form in which you enter the date; enter
it as 2/1/2009, for example. Then you can use a Custom format to make
the cell appear the way you want (well, almost).

Caveat: With the Custom format MMM-DD-YYYY, you will see
Feb-02-2009. If the leading zero in the day is not acceptable, post
back for assistance. There may be a Custom format that I'm not aware
of.
mmm-d-yyyy



Alternatively, you have to do a lot more work to do the computation.
For example:

=B1 +
date(mid(A1,8,4),
int((find(mid
(A1,1,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3)+1,
mid(A1,5,2))

Format as Custom MMM-DD-YYYY, with the caveat again that days less
than 10 will have a leading zero.

or

=SUBSTITUTE(SUBSTITUTE(A1,"-",", ",2),"-","")+B1

But it really depends on how he entered the data.
--ron

 

[CurlyDave]

Jou

I am not getting the same result as you. I am getting #VALUE!
in the cell even though i format it as date




=A1+B1

Format as Date, if necessary.

Obviously, your cells are not dates, but text

 

[joeu2004]

mmm-d-yyyy

I am aware of that.

I was referring to the fact, I thought, that the OP had wanted the
form "Feb- 2-2009" for single-digit dates -- a blank before the
digit. That makes some sense to me, if the OP wants everything to
line up.

But looking back, I see that the OP wrote "Feb -1-2009". My guess
is: that's either a typo or a posting anomaly.

or
=SUBSTITUTE(SUBSTITUTE(A1,"-",", ",2),"-","")+B1

Which I believe needs to be formatted as Date -- and as a Custom date
format if the OP wants the format to look like he specified (sort
of). Alternatively:

=SUBSTITUTE(TEXT(SUBSTITUTE(SUBSTITUTE(A1,"-",", ",2),"-","")
+B1,"mmm-dd-yyyy"),"-0"," -")

Now there's a mouthful. So much better to steer the OP toward a
custom format, IMHO.

 

[Ron Rosenfeld]

I am aware of that.

I was referring to the fact, I thought, that the OP had wanted the
form "Feb- 2-2009" for single-digit dates -- a blank before the
digit. That makes some sense to me, if the OP wants everything to
line up.

Ah - that was not clear to me. And no, I don't know of a single format that
would allow the dates to "line-up" and also allow just a single digit for the
day. However, it could be done by using event code to set two different
formats depending on whether day >=10.

But looking back, I see that the OP wrote "Feb -1-2009". My guess
is: that's either a typo or a posting anomaly.



Which I believe needs to be formatted as Date -- and as a Custom date
format if the OP wants the format to look like he specified (sort
of).

I thought that would have been understood.

Alternatively:

=SUBSTITUTE(TEXT(SUBSTITUTE(SUBSTITUTE(A1,"-",", ",2),"-","")
+B1,"mmm-dd-yyyy"),"-0"," -")

Now there's a mouthful. So much better to steer the OP toward a
custom format, IMHO.

Agree.

Entering the dates as dates would be even more preferable.
--ron