98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Dates
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 information:
See also:
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:3D formulas find and replaceAfter 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. |