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.

 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
Cross-posted here: https://stackoverflow.com/questions...-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).
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top