Having a returned date format actually equal itself

[Parafly9]

I have a spreadsheet that has a running column of dates, e.g. 1/1/07, 1/2/07, etc.

I have associated columns that break this out for pivot-table purposes into things like WEEKNUM. I also have columns that just reference this column with the formatting changed to something custom like DDD or YYY to return just the year or the day of the week.

Problem is in my pivot table, when I use these DDD or YYY columns as a parameter, it actually uses the underlying valu, which si the date.

So, I can't sort by volume on Monday because excel doesn't see my cell as MON, it sees it as 1/1/07, 1/8/07, 1/15/07, etc.

Ideas/ ?

 

[brian.wethington]

Helper column using
=weekday(Range) then you can use 2 as Monday

 

[Richard Schollar]

Or use the Text function to return your weekday eg:

=TEXT(A1,"dddd")

 

[Norie]

Why not use the pivot tables grouping features?

 

[brian.wethington]

Perhaps I am being a little naive... can this be done by monday's, tuesdays.....

I can't only group by week 1/01 to 1/07 or something like this. am I missing it?

 

[Norie]

Brian

I'm not 100% sure about all the grouping functionality for pivot tables.

But then again I'm not 100% sure what the OP actually wants to do.

Grouping just by a weekday doesn't really make much sense to me.

I mean I think you'd end up grouping by Monday or whatever for all months and years the data covers.

 

[Parafly9]

TEXT worked. Weekday also worked, but i had a similar column with month and using =TEXT(A1,"mmm") worked great. Thanks!

 

[Parafly9]

I think its a good idea; but I am trying to be able to use this pivot table for as much data as possible, including pulling graphs on if you wanted Mondays in December vs. Tuesdays in February or project-length. Anyways I got it working; thanks!