[SOLVED] Creating week number starting on Jan 1 to Jan 7 as week one

[jackson_hollon]

My boss wants me to create a spreadsheet with any given date and return week number that starting on Jan 1 to Jan 7 as week one and so forth.

For example, putting Jan 15, it will return week number 3.


Can someone tell me how to do it? Thanks a lot.

[samba_ravi]

=int((a1-date(year(a1),1,1))/7)+1

[samba_ravi]

Attach sample file

[Md Aejaz]

If it has to take monday as the first day of every week,use this=DATE(YEAR(A1),MONTH(A1),DAY(A1)-WEEKDAY(A1,3)).

[Special-K]

Just use 2018 as the default year as 01/01/2018 will be a Monday

=WEEKNUM(DATE(2018,MONTH(A1),DAY(A1)))

[daddylonglegs]

Just use 2018 as the default year as 01/01/2018 will be a Monday

=WEEKNUM(DATE(2018,MONTH(A1),DAY(A1)))

It's a nice idea but I don't think that will work, firstly WEEKNUM defaults to a Sunday start, so you either need to find a year which starts with a Sunday or use 2 as the second argument of WEEKNUM.........but even then leap years will mess you up so I'd endorse nflsales proposed solution which should work for any date in any year

(of course you always end up with the last day, or two days in leap years being in week 53)

[jackson_hollon]

nflsales, Md Aejaz, Special-K and Audere,

Thank you very much for the help!

Jackson