WEEKNUM for Date Range for each month

**savetrees** · 09-26-2014, 03:05 AM

Team,

I am searching through to get the excel formula to calculate the WeekNumber for each month beginning to end date. Week beginning day would be Monday and ending would be Sunday.

The Requirement is

Jul-2014 - Weeknumber for each date beginning from 01-Jul-2014 to 31-Jul-2014 (01-Jul-2014 to 06-Jul-2014 - Week1 and 07-Jul-2014 to 13-Jul-2014 - Week2 so on)
similarly
Aug-2014 -Weeknumber for each date beginning from 01-Jul-2014 to 31-Jul-2014 (01-Aug-2014 to 03-Aug-2014 - Week1 and 04-Aug-2014 to 10-Aug-2014 - Week2 so on)

Challenging part is i have the Date range starting from 01-Jul-2014 and go on forever.

**Crooza** · 09-26-2014, 03:15 AM

Try this

Please Login or Register  to view this content.

Where A1 is the date you're checking - this will return the week number for the month that date is in

**savetrees** · 09-26-2014, 03:18 AM

Perfect and clean .. thanks Crooza

**daddylonglegs** · 09-29-2014, 05:57 AM

Originally Posted by savetrees

.....01-Jul-2014 to 06-Jul-2014 - Week1 and 07-Jul-2014 to 13-Jul-2014 - Week2 so on...

I don't think Crooza's suggested formula fulfils that requirement

You say that 7/7/2014 is in week 2 but the formula will put it in week 2, it takes no account of the day of the week. If you want week 1 to start on the 1st of every month and week 2 to start on the following Monday then try this formula:

=INT((13+DAY(A1)-WEEKDAY(A1-1))/7)

**savetrees** · 09-30-2014, 12:31 AM

Hey daddylonglegs ... thanks for your updates and solution to it .. it is indeed was missing and i did not notice, because my data with dates were non-series and thanks for your detailed review. cheers

WEEKNUM for Date Range for each month

LinkBack

Thread Tools

Rate This Thread

Display

WEEKNUM for Date Range for each month

Re: WEEKNUM for Date Range for each month

Re: WEEKNUM for Date Range for each month

Re: WEEKNUM for Date Range for each month

Re: WEEKNUM for Date Range for each month

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculating stats by week instead of month - WEEKNUM?

Array function for date(month), Range Date(month)

Year, Month, Day, Weeknum functions in a macro

Custom function for weeknum to Month i got some issues with this need alittle help

weeknum for month

Bookmarks

Bookmarks

Posting Permissions