need formula to add dates

  • Thread starter Thread starter sqlpro
  • Start date Start date
S

sqlpro

Hi Friends

I've a excel sheet 3 columns

startdate duration enddate
29/08/2003 12 27/08/2004
18/03/2003 3 16/06/2003
29/09/2004

now how can i write formula that will add 364 days to startdate i
duration is 12 if it is 3 then 90 days otherwise empty into enddat
column

Thank
 
Hi,

Not sure whether Excel has a feature similar to Lotus, but
@NEXTMONTH(A1,B1,1) in lotus adds the number of months in cell B1 to the
date in A1.

Rob
 
Hi

Are you sure you don't want enddate to be the date x month after startdate?

=IF(OR(A2="",B2=""),"",MIN(DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)),DATE(YEAR(A2)
,MONTH(A2)+B2+1,0)))
returns nothing, when startade or duration are empty, otherwise the same day
after x month or last day of month after x month - depending the number of
days in enddate month.
 
Hi Friends

Thank you so much for all ur help.Its working fine
but i have one quick question.

the some dates in "startdate" column are left aligned and some ar
right aligned in my exce sheet.

The formula u guys suggested worked fine if dates are right aligned i
the column.

my question is how can format all the rows in this col to date.

Yes,i tried format cells option on the column but did not work on date
that r left aligned.
I believe excel treating them as strings not dates!!
Any ideas friends
Thank
 
Hi

A part of dates are texts. After you format such cells as dates, you have to
start edit mode for cell (select the cell and press F2), and re-enter the
formula (simply press enter).

To convert all datetexts to dates on a single go, format the range with
would-be dates as date, enter a number 1 into some free cell and copy it,
select the range with datetexts (real dates can also remain selected), and
then use PasteSpecial.Multiply on whole range. (Don't forget to erase
entered number 1 afterwards.)


Arvi Laanemets
 
Hi Arvi

I just tried ur suggestion but it changing all dates to numbers!

what i did was i entered 1 in a free cell and i selected "startdate
column (which contains actual dates and text dates) and right click an
paste special and selected "multiply"

it changed all dates to numbers but it did not touch datetexts rows.

am i doing any wrong Arvi
Thank
 
Hi

Obviously those "datetext" entries contain some additional characters, like
spaces, hard spaces etc. Check for length of them - when this differs from
number of visible characters, then try to find with formula out, what
characters they are. An example of such formula
=CODE(RIGHT(A1,1))
returns ASCII code for leftmost character in cell A1.

Then you can select daterange, and replace all abundant characters with
nothing (empty string), and then try again to convert dates.
 
Hi Arvi
Arvi said:

=CODE(RIGHT(A1,1))
returns ASCII code for leftmost character in cell A1.
Click to expand...

I tried like following
=CODE(LEFT(AU2,7))
but the above always returning me 32 even though there is some text a
7th place.
Then you can select daterange, and replace all abundant character
with
nothing (empty string), and then try again to convert dates.
Click to expand...
Can you tell me how can i do that please
Thanks for ur time
 
Hi

CODE function returns ASCII code for leftmost character in string - i.e. in
your case for "A". The rest of string doesn't affect the result at all.

I had a typo in my posting too. You have to read 'returns ASCII code for
rightmost character in cell A1.' there.


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)


sqlpro > said:
Hi Arvi
Arvi said:

=CODE(RIGHT(A1,1))
returns ASCII code for leftmost character in cell A1.
Click to expand...

I tried like following
=CODE(LEFT(AU2,7))
but the above always returning me 32 even though there is some text at
7th place.
Then you can select daterange, and replace all abundant characters
with
nothing (empty string), and then try again to convert dates.
Click to expand...
Can you tell me how can i do that please
Thanks for ur time
Click to expand...
 
Back
Top