first of the month
 

Excel 2003
I have a status date in one column and need to create a start date of the
first of the month of the status date in another column. For example, status
date is 01/15/1999 the start date should be 01/01/1999. I have used the
concatenate feature, pulling the month and year and inserting text "/01/" in
the middle, but it uses the serial? date number instead. I saw a formula for
the end of the month, is there one to give me the first of the month? If I
put the apostrophe in front of the status date it works, but setting the cell
format as text only shows me the serial? date number. I have over 10,000 rows
to deal with so I'm not going through this manually to add the apostrophe!

Thanks,

first of the month
 

Hi ClaireKG

You can use this formula with the date in A1
=DATE(YEAR(A1),MONTH(A1),1)

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"ClaireKG" wrote in message ...
Excel 2003
I have a status date in one column and need to create a start date of the
first of the month of the status date in another column. For example, status
date is 01/15/1999 the start date should be 01/01/1999. I have used the
concatenate feature, pulling the month and year and inserting text "/01/" in
the middle, but it uses the serial? date number instead. I saw a formula for
the end of the month, is there one to give me the first of the month? If I
put the apostrophe in front of the status date it works, but setting the cell
format as text only shows me the serial? date number. I have over 10,000 rows
to deal with so I'm not going through this manually to add the apostrophe!

Thanks,

first of the month
 

=DATE(YEAR(A1),MONTH(A1),1)

"ClaireKG" wrote:

Excel 2003
I have a status date in one column and need to create a start date of the
first of the month of the status date in another column. For example, status
date is 01/15/1999 the start date should be 01/01/1999. I have used the
concatenate feature, pulling the month and year and inserting text "/01/" in
the middle, but it uses the serial? date number instead. I saw a formula for
the end of the month, is there one to give me the first of the month? If I
put the apostrophe in front of the status date it works, but setting the cell
format as text only shows me the serial? date number. I have over 10,000 rows
to deal with so I'm not going through this manually to add the apostrophe!

Thanks,

first of the month
 

Perfect! Thanks to both!

Claire

"Teethless mama" wrote:

=DATE(YEAR(A1),MONTH(A1),1)

"ClaireKG" wrote:

Excel 2003
I have a status date in one column and need to create a start date of the
first of the month of the status date in another column. For example, status
date is 01/15/1999 the start date should be 01/01/1999. I have used the
concatenate feature, pulling the month and year and inserting text "/01/" in
the middle, but it uses the serial? date number instead. I saw a formula for
the end of the month, is there one to give me the first of the month? If I
put the apostrophe in front of the status date it works, but setting the cell
format as text only shows me the serial? date number. I have over 10,000 rows
to deal with so I'm not going through this manually to add the apostrophe!

Thanks,

first of the month
 

With the date in A1:
=a1-Day(a1)+1

Say your date is January 15, 1999.

It subtracts 15 from that date giving you Dec 31, 1998. Adding one gives you
Jan 1, 1999.



ClaireKG wrote:

Excel 2003
I have a status date in one column and need to create a start date of the
first of the month of the status date in another column. For example, status
date is 01/15/1999 the start date should be 01/01/1999. I have used the
concatenate feature, pulling the month and year and inserting text "/01/" in
the middle, but it uses the serial? date number instead. I saw a formula for
the end of the month, is there one to give me the first of the month? If I
put the apostrophe in front of the status date it works, but setting the cell
format as text only shows me the serial? date number. I have over 10,000 rows
to deal with so I'm not going through this manually to add the apostrophe!

Thanks,

--

Dave Peterson