• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Week number

K

KVAMSHE

New Member
Hello,


Am looking for a formula which gives me no.of weeks in a month i.e. week1, week2, week3,week4 & week5 based on date likewise for full year.


Ex: I have a data for full year that contains no.of tickets processed on each workday. Based on date i.e. 1-Jan-13, i want to contruct a formula which showcase the given data in below format:


Month Week1 Week2 Week3 Week4 Week5

Jan x x x x x

Feb x x x x x

Mar x x x x x

Apr x x x x x

May x x x x x

Jun x x x x x

Jul x x x x x

Aug x x x x x

Sep x x x x x

Oct x x x x x

Nov x x x x x

Dec x x x x x


Thanks in advance.
 
Hi Vamshe ,


Can you specify your idea of Week 1 ? Is it the week which has Jan 1 in it ?


Thereafter , when does Week 2 start ? On Monday ?


Narayan
 
Hi, KVAMSHE!

Depending on your definition of week, months could have 4, 5 or 6 weeks, if full or partial weeks (and starting or ending) are considered. So just in case prepare a new Week6 column for an extra "x".

Regards!
 
1. How is the data stored as I would look at an additonal field which converts the date format to mmm-yy giving you the field month-year.


When you then look at the data from a pivot table the item field month-year will group the data for you.


I have this type of data stored in an Access Db yet you can do this with data in a excel spreadsheet and a formula to the side =a1 and copy down then format cell / number / custom choose mmm-yy


Hope this helps else will need more info. Thanks.
 
Hello,
Date in cell A1 then paste below Formula in cell B1
="Week -"&ROUND((DAY(A1)-WEEKDAY(A1,2))/7+0.4999,0)+1
Hope this helps
 
Hello Debraj,

I Apologized for the confusion, I tried to help but due to some error I'M not able to upload the sample file.
 
You must log in or register to reply here.
Back
Top