VBA If new week (Sunday) then cell value = "1".. "2"..

[Lewzerrrr]

Hey,

Not sure how to go about this but I need to populate a cell with the value of 1 - 52 using my work calendar.

Currently I'm in week 1 so throughout this week, whenever I open the workbook A1 needs to be "1" and then on Sunday - Saturday if I'm to open the workbook it needs to be "2".. following Sunday - Saturday onwards "3".

Any help would be appreciated!

Thanks,

 

[Scott Huish]

Can't you just use the WEEKNUM function?

=WEEKNUM(TODAY())

 

[MrTeeny]

Try this formula and amend 27/08/2017 to whatever your start date is , I'm in UK so it's entered as UK format but put in your own regions formatting

=ROUNDDOWN((DATEDIF("27/08/2017",TODAY(),"d")/7),0)+1

 

[MrTeeny]

Scott's idea might be best as it's an excel function, it starts from 1 Jan but you can tweak it by subtracting the number of days between 1 Jan and 27 Aug to customise it

=WEEKNUM(TODAY()-238)