date functions

[Kevin]

I'm creating a sheet to track expenditures. I'd like to enter an exact
invoice date (e.g. 02/07/2004), but then have any sorting, or subtotals only
use the month. I know I can do it manually, but I'd like it a bit more
automatic. For example to simply use the data/subtotals menu and have it
create a subtotal when the month changes. Or to be able to pull the data
into a pivot using the month.

Thanks

--

 

[Chip Pearson]

Kevin,

One way to do this would be to use the MONTH function in an extra
column, and then sort or pivot on that columns. E.g.,

=MONTH(A1)

will return the month number (1-12) of the date in cell A1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kevin]

Yeah I thought about that, but that would mean continuously cut/pasting the
formula down in the raw data sheet. I was hoping to keep the data entry
portion free of any formulas. That way I can have each individual go to
their respective sheet and add a row at the bottom. That also makes the
pivot display 1-12. What I'd really like the pivot to display is "January
04"

I'm facing the exact same issue on another solution where the source data is
imported, which of course then doesn't have the extra field in it with the
function.

Thanks for your help

Kevin,

One way to do this would be to use the MONTH function in an extra
column, and then sort or pivot on that columns. E.g.,

=MONTH(A1)

will return the month number (1-12) of the date in cell A1.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kevin]

Thanks Chip. I solved it by creating a list so that the formulas are copied
down when the user adds records. I also added a lookup to convert the month
number back to the textual name, so that the pivot reads "January".

Still trying to find a way to solve the same issue when importing data.
I've got a network monitor that exports response time. I import that and
then pivot to get various metrics over time. The main one is
availability/month. The export has the date field. Currently I do what you
recommend, but have to cut/paste the formula down after every data import.
In an ideal world, I'd just click "import" and then refresh the pivot. I'm
really surprised that there isn't a built in function.

Yeah I thought about that, but that would mean continuously cut/pasting the
formula down in the raw data sheet. I was hoping to keep the data entry
portion free of any formulas. That way I can have each individual go to
their respective sheet and add a row at the bottom. That also makes the
pivot display 1-12. What I'd really like the pivot to display is "January
04"

I'm facing the exact same issue on another solution where the source data is
imported, which of course then doesn't have the extra field in it with the
function.

Thanks for your help