Excel WEEKNUM (Date & Time) Question

  • Thread starter Thread starter Bcel
  • Start date Start date
B

Bcel

Hello there

I have to calculate the week number for a given date but the catch is
the week has to work from FRIDAY to THURSDAY.

Using the WEEKNUM function is great for a Sunday or Monday start. Is
there a simple solution??

I have a cell for the actual date and a cell with its number VALUE.

thanks
 
Does week 1 start on the first Friday of the year, or the Friday before the
1st of the year (unless that is a Friday), or some other rule?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
hello there

I have to create a new payroll spreadsheet and need to record the day
of work referenced to a week number. Taking the start of this year fo
example, work done up to Thursday 01 would be done in week one and pai
on Friday 02. Work done between Friday 02 and Thursday 08 would be don
in week 2. It would just keep following on into the next year with th
same requirement.

many thank
 
Then Mike's formula fits the bill. Have you tried his formula?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello Bob

I may have missed a reply or just misunderstood. Where and what is
Mike's formula you refer to in your reply.

Bcel
 
This is it

=WEEKNUM(A1+2,1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bcel!

You're going to get problems here, whatever you do.

A1: Tue 30-Dec-2003
=WEEKNUM(A1+2,1)
Returns 1

Also you'll get weeks with less than seven days in them.

But it seems that you are only concerned with Thursday and Friday here
and you want a system with every week containing 7 days. If your not
stressed out at rolling back to week 1 each year, you can just select
a base date Friday and work from there:

=INT((A1-BaseDate)/7)+1

If your BaseDate is a Friday, then on each Friday you'll get a change
of week number. I picked Fri 25-Feb-2000 as my BaseDate and using that
my week numbers always escalate by 1 on a Friday.

But there is an alternative approach.

If you use the ISO8601:2000 week numbering you will find that Thursday
and Friday will always fall in the same weeknumber and every week will
have 7 days in it.

You can use the following formula by Evert van de Heuvel to get the
ISO weeknumber:

=1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+WEEKDAY(DATE(YEAR(A1+4-W
EEKDAY(A1+6)),1,3)))/7)

You could play around with the ISO algorithm and get all weeks to
start on a Friday but why not play around with your definitions?

Thursday 1-Jan-2004 is end of payweek 1
Friday 2-Jan-2004 is payday for payweek 1

Your weeknumbers can come from the ISO formula and consecutive Thurs
and Fri will always have the same weeknumber. All week numbers will
always be consecutive with exception that after 53 (or in some cases
52) it starts back at 1. The only complication to get your head around
is the fact that week 1 of 2004 started in 2003.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top