Week Number

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

A.W.J. Ales

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

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I

ianripping

Could this be adapted so that if there isnt a date in A Column then the
cell displays nothing.
 
I

ianripping

Norman, your for,ula comes out with a very odd number.

Can you recheck.

Your formula is the type I need
 
I

ianripping

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

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top