Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Round date to first of month
Hey all,
I'm trying to round date entries to the first of the month so that I can PivotTable on the date field. I've tried using the Month and Year functions in a formula in a helper column but the result isn't recognized as a date (even if I add a "1" in between the two). I can't seem to find the answer in a previous post. Does anyone have any ideas? Thanks in advance. A simple example follows: A B C Sacramento 8/4/04 $45 Sacramento 8/9/04 $65 San Francisco 9/1/04 $200 Los Angeles 12/3/04 $60 Los Angeles 12/10/04 $75 I'm trying to get the PivotTable to sum on 8/04 or 8/1/04, 9/04, and 12/04. Thanks again. |
#2
|
|||
|
|||
Dominic
if the date is in cell B1, try: =DATE(YEAR(B1),MONTH(B1),1) in your helper column Regards Trevor "Dominic" wrote in message ... Hey all, I'm trying to round date entries to the first of the month so that I can PivotTable on the date field. I've tried using the Month and Year functions in a formula in a helper column but the result isn't recognized as a date (even if I add a "1" in between the two). I can't seem to find the answer in a previous post. Does anyone have any ideas? Thanks in advance. A simple example follows: A B C Sacramento 8/4/04 $45 Sacramento 8/9/04 $65 San Francisco 9/1/04 $200 Los Angeles 12/3/04 $60 Los Angeles 12/10/04 $75 I'm trying to get the PivotTable to sum on 8/04 or 8/1/04, 9/04, and 12/04. Thanks again. |
#3
|
|||
|
|||
=DATE(YEAR(B2),MONTH(B2),1)
should do it -- HTH RP (remove nothere from the email address if mailing direct) "Dominic" wrote in message ... Hey all, I'm trying to round date entries to the first of the month so that I can PivotTable on the date field. I've tried using the Month and Year functions in a formula in a helper column but the result isn't recognized as a date (even if I add a "1" in between the two). I can't seem to find the answer in a previous post. Does anyone have any ideas? Thanks in advance. A simple example follows: A B C Sacramento 8/4/04 $45 Sacramento 8/9/04 $65 San Francisco 9/1/04 $200 Los Angeles 12/3/04 $60 Los Angeles 12/10/04 $75 I'm trying to get the PivotTable to sum on 8/04 or 8/1/04, 9/04, and 12/04. Thanks again. |
#4
|
|||
|
|||
Many thanks to you and Trevor. Works great.
"Bob Phillips" wrote: =DATE(YEAR(B2),MONTH(B2),1) should do it -- HTH RP (remove nothere from the email address if mailing direct) "Dominic" wrote in message ... Hey all, I'm trying to round date entries to the first of the month so that I can PivotTable on the date field. I've tried using the Month and Year functions in a formula in a helper column but the result isn't recognized as a date (even if I add a "1" in between the two). I can't seem to find the answer in a previous post. Does anyone have any ideas? Thanks in advance. A simple example follows: A B C Sacramento 8/4/04 $45 Sacramento 8/9/04 $65 San Francisco 9/1/04 $200 Los Angeles 12/3/04 $60 Los Angeles 12/10/04 $75 I'm trying to get the PivotTable to sum on 8/04 or 8/1/04, 9/04, and 12/04. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
How do I copy only the month from a date | Excel Worksheet Functions | |||
Lookup the month in a date string 01/03/05 | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions | |||
Return the end of month date from a date | Excel Worksheet Functions |