Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Last day of this month to last day of last month | Excel Discussion (Misc queries) | |||
Finding values based on current month | Excel Worksheet Functions | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions |