Week formula for excel

[rugbyno9]

I have a list of dates that I want to summarize by week in a pivot table. Is there any way to calculate the week based on a date?

Thanks,
John Fitzgerald

 

[mymou]

Cant you use =WEEKNUM(cell)?


Stew

 

[DaleWatson123321]

Hi John,

Stew's formula is fine IF you have a version of Excel which has the WEEKNUM function. My version, Excel 97, does not.

I therefore offer up the following formula for those Excel users who do NOT have such a WEEKNUM function:

=MIN(52,INT((A1-DATE(YEAR(A1),1,1))/7)+1)

Note: As every year has slightly more than 52 weeks, the MIN function effectively assigns the extra day (2 days in the case of a leap year) to the last week of the year.

I hope this is helpful.

If you have any problem or question, please let me know.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rugbyno9]

Thanks to both of you. I don't have the weeknum formula, and I ended up creating a new column in my database for week so I'm using that but your formula will be great for the next time.

John Fitzgerald

 

[mymou]

Thought that was too easy.

Stew

 

[palotasani]

Hi Dale,

WEEKNUM is an add-in being part of Analysis Toolpak.
Well, if you install the Analysis Toolpak and/or check it in the Tools|Addins menupoint, you will also able to use the WEEKNUM.

Hoping i was helpful
Anna
palotasani@hotmail.com Anna Palotas
IT Engineer