dates

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# Dates

Resolved · Medium Priority · Version 365

Pia has attended:
Excel Intermediate course

### Dates

Is it possible to have the week of the year start mid-year? Our working year starts on the first week of august so we would like that to be week 1

### RE: Dates

Hi Pia,

Welcome to the Support Forum and thank you for the question.

If you want the first week in August to be week 1 then you need to use the WEEKNUM function. This will turn any date to the equivalent week number starting in January by default.

The question is if you want week 1 to start on 01/08/23 and end on 07/08/23 then you need to include in the WEEKNUM a number which represents the first day of the week. So 01/08/23 falls on a Tuesday therefore it would be =WEEKNUM(date,12)

Let's say you had a range of dates from 01/08/23 to 31/07/24 covering the cell range A1:A366. The formula in cell B1 would be:

=IF(WEEKNUM(A1,12)>=32,WEEKNUM(A1,12)-31,WEEKNUM(A1,12)+21)

Because the week number of 01/08/23 - 07/08/23 is normally 32 then subtract 31 to get 1 for that first week, subtract 31 from the next week to get 2 etc.

This works when you copy down the formula into the next year. However where it falls down at the end is the last 2 days of July become 1 when they should be 53. These could be changed manually and then for the following year starting in 01/08/24 the weekday is Thursday so the formula would need to change to =WEEKNUM(date, 14). The last day in July 2025 would need to be adjusted to week 53 and then the formula for the dates from 01/08/25 would have to be changed again to allow for this date starting on a Friday (equivalent number in the WEEKNUM is 15)

I hope this helps. Please let me know if it has worked or not

Kind regards
Martin

### Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### 3D formulas find and replace

After you know all the components of a 3D reference, you can change them to suit by using a localised Find and replace crt+f, if need be.

View all Excel hints and tips