Find week number based my current financial year start date in VBA/Excel

VBALearn1

New Member
Joined
Jul 17, 2019
Messages
1
hi Everyone,
The issue is Sep 6 2019 needs to display as Week 2 of Sep, but I am getting Sep Week 1. Week no will change
on every Saturday. my Financial calendar is 4-4-5 basis. our 2019 financial year is starting on 29 Dec 2018 to
Dec 27 2019, so based on this period, 29 dec 2019 to 4 jan 2019 is the Week 1 of Jan 2019, from 5 th Jan 2019
week 2 will start. like this I need to show Sep 6 as week 2.

my current project year starts on Sep 6. have 104 weeks in excel sheet to enter weekly hours. these weeks
will generate based on the start date(Sep 6 2019). 7 days will add to the previous week, like this 104 weeks
will
create.

Below code is returning Sept week 1 as per ISO week nos. But i need to display as Sept week 2
weekNo= Trim(Format(dateCell.Value, "\ mmm " & "\W" & DatePart("ww", dateCell) - DatePart("ww",
DateSerial(Year(dateCell), Month(dateCell), 1)) +1))

It will be grate help if I get any Excel formula/VBA code to get the week no. I have searched lot of forums
for this.

 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
727
Office Version
2007
Platform
Windows
I suggest you create an offset to be added to the dateCell value and then use this amended value in your calculation. In your example the offset would be 3 (1 jan 2019 - 29 dec 2018). This could be created by having a formula to subtract the financial year start from 1st. January.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,685
Office Version
365
Platform
Windows
Cross-posted here: https://stackoverflow.com/questions/57073772/find-the-week-no-in-a-month-based-on-the-financial-calendar-in-vba

Whilst we allow cross-posting here (most forums don't) we do require that you make it clear when you do and that you provide link references to the threads at the other forums. This is so that members here can see what has already been suggested or even avoid spending time unnecessarily should you already have a suitable answer (as appears to be the case looking at your post over at Stack Overflow).
 

Forum statistics

Threads
1,078,239
Messages
5,339,027
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top