Week Number

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

In column A, I have dates entered by staff into the cells.

In Column B, they work out which week in the month it is and enter this
into these cells.

Is it possible that excel can work out the week number via a formula?
 
Ian,

Provided your weeks start at mondays use :
=WEEKNUM(A1,2)

If they start at sundays : =WEEKNUM(A1,1)

Both formula's assume that your date is in A1

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi Ian!

From what you say, you want the week in the month. Adopting day 1 as
day 1 of week 1:

=INT((A1-DATE(YEAR(A1),MONTH(A1),1))/7)+1

You should appreciate that week 5 will always have an odd number of
days.

For other formulas regarding days of the month see:

Dave McRitchie:
http://www.mvps.org/dmcritchie/excel/datecalc.htm

--
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.
 
Could this be adapted so that if there isnt a date in A Column then the
cell displays nothing.
 
Norman, your for,ula comes out with a very odd number.

Can you recheck.

Your formula is the type I need
 
OK sorry, I entered it incorrect.

So could we make it so that if no date is entered into A1 then the
formula posts nothing instread of a 0?
 
Hi Ian!

I'm assuming that you mean "if there's no entry in A1"

The standard approach to this is:

=IF(A1="","",YourFormula)

So try:

=IF(A1="","",INT((A1-DATE(YEAR(A1),MONTH(A1),1))/7)+1)

Alternative is just testing for empty cells using ISBLANK(A1).

Now to remove the assumption and cover your question as "if there is
no date in A1."

There isn't an inbuilt function for testing for a date but you can
have the following function:

Function ISADATE(MyCell As Range)
ISADATE = IsDate(MyCell)
End Function

Then:

=IF(ISADATE(A1),INT((A1-DATE(YEAR(A1),MONTH(A1),1))/7)+1,"")

But I'm not really happy with the ISADATE function because it resolves
to #NUM! for numbers outside the date range.

=IF(ISERROR(IF(ISADATE(A1),INT((A1-DATE(YEAR(A1),MONTH(A1),1))/7)+1,""
)),"",IF(ISADATE(A1),INT((A1-DATE(YEAR(A1),MONTH(A1),1))/7)+1,""))

Looks complicated but it's only using structure:

=IF(ISERROR(YourFormula),"",YourFormula)


--
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