Formula for week commencing not working

Hi Expert (using excel 2003 or 07)

I have this formula which is coming back with a #Ref error and i am totally lost on what investigation to do to resolve this...

=MAX(WeekCommencing)+(7-WEEKDAY(MAX(WeekCommencing),2))
route217JuniorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

What does the named range [WeekCommencing] refer to?

Or do you not have that range defined at all?

BFN,

fp.
0
route217JuniorAuthor Commented:
hi fp

good question...let me check...if the week commencing refers to anything..
0
route217JuniorAuthor Commented:
ok fp

weekcommcencing refers to =OFFSET(Data!$A$1,1,0,COUNT(Data!$A:$A),1)

where the dates are in the format: 2012-04-24


regards
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

[ fanpages ]IT Services ConsultantCommented:
Thanks.

Like the attached workbook?

I have defined the named range [WeekCommencing] at the worksheet [Data] level; not at the workbook level.
Q-28251386.xls
0
route217JuniorAuthor Commented:
ok how do you define at worksheet level and not workbook level

what are the steps - please fp...
0
barry houdiniCommented:
If you get #REF! that might mean there are no valid dates in Data!$A:$A. Perhaps those dates are formatted as text. What result do you get if you put this formula somewhere on the worksheet?

=COUNT(Data!$A:$A)

If you get zero it means there are no valid dates and your formula won't work. How are the dates generated?

regards, barry
0
[ fanpages ]IT Services ConsultantCommented:
ok how do you define at worksheet level and not workbook level

what are the steps - please fp...

Which version of MS-Excel are you using?
0
route217JuniorAuthor Commented:
excel 2003
0
route217JuniorAuthor Commented:
Hi Barry

I do get a zero on the count formula and the date are populated via a qry from ms access...refreshed.

and format cell cutom is dd/mm/yyyy hh:mm
0
barry houdiniCommented:
That means your dates are not recognised as dates. You can try using "Text to columns" to convert. Select date column and then use

Data > Text to columns > Finish

That should convert to valid dates and formula should work......

You may have to re-format the dates in a format of your choosing.....

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
where the dates are in the format: 2012-04-24

and format cell cutom is dd/mm/yyyy hh:mm

Something is not right here!
0
barry houdiniCommented:
fp,

If the dates are actually formatted as text, which I think they are then the "format" is fixed and won't change whatever date formatting is selected so I think those two statements can both be correct at the same time assuming "format" in the first statement simply means how they look.....

regards, barry
0
[ fanpages ]IT Services ConsultantCommented:
Yes barry, that is why I provided the workbook as an example so that route217 could confirm what was seen within the local workbook (that we have not had sight of [yet]).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.