dates

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

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dates

Dates

resolvedResolved · 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.


 

Excel tip:

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


Server loaded in 0.07 secs.