calling all excel gurus

[TdC]

peeples, I've discovered that once again I am blocked by my own creativity.

for a certain purpose, I have created the following formula:

=IF(WEEKDAY(G3,2)=1,TRUNC(((G3-DATE(YEAR(G3),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(G3),1,1))>WEEKDAY(G3),1,0),"")

the output of this formula in this case happens to be

2

what I want it to say is

week 2

in the same cell!!!!

BUT I CANT GET IT TO DO THAT AARGH(^$(%&$(%&$(

HELP ME BEFORE I COMMIT MURDER

 

[caLLous]

Um well you would put

"week "&

in there to concatenate the result onto "week ". I didn't go through your formula but if you have a value in B2 and you want C2 to be "week b2" you would say

="week "&B2

in C2.

 

[TdC]

thank you caLLous. you just saved 37 lives!

 

[Zenith.UK]

="Week "&IF(WEEKDAY(G3,2)=1,TRUNC(((G3-DATE(YEAR(G3),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(G3),1,1))>WEEKDAY(G3),1,0),"")

There ya go.

[edit] I went away to doublecheck and got snipered on the post. Sorry for the duplication.

 

[Jupitus]

Well, yes to what Callous said probably, but what the piffle are you trying to do with that abominable formula???

 

[caLLous]

=IF(WEEKDAY(G3,2)=1,"week "&TRUNC(((G3-DATE(YEAR(G3),1,1))/7))+1+IF(WEEKDAY(DATE(YEAR(G3),1,1))>WEEKDAY(G3),1,0),"")

That works for 2, 9, 16 etc but your formula is making my head hurt slightly.

 

[Jupitus]

Just a quick note - have you deliberately used the return type for the weekday function as 2 for the first use, then just the default weekday return type thereafter, or is that a mistake?

 

[TdC]

well, it could probably use some tweaking, but what it basically does is, in pseudocode:

if some cell contains a date that happens to be a monday, 
then let the contents of this cell be "week " and whatever week it happens to be by working it out in a roundabout way

there's prolly a function that directly returns the week number, but I'm pooh at excel so there.

 

[TdC]

Just a quick note - have you deliberately used the return type for the weekday function as 2 for the first use, then just the default weekday return type thereafter, or is that a mistake?

no, it's deliberate. the first one checks for type 2, eg "Monday"

 

[Jupitus]

You'll want to put the "Week "& into the return part of the IF statement then, otherwise the other cells will contain 'Week'.... guess you've figured that part

 

[TdC]

yes, I figured that part

 

[pcg79]

well, it could probably use some tweaking, but what it basically does is, in pseudocode:

if some cell contains a date that happens to be a monday, 
then let the contents of this cell be "week " and whatever week it happens to be by working it out in a roundabout way

there's prolly a function that directly returns the week number, but I'm pooh at excel so there.

=IF(WEEKDAY(G3,2)=1,"week "&weeknum(g3,2),"")

 

[Jupitus]

Nice one dude

 

[TdC]

friggin' ace pcg79, thanks for that

now to see if workies' ancient excel can actually do that o0

 

[TdC]

sadly, our super dinosaur excel has WEEKDAY but not WEEKNUM

 

[ST^]

It should be part of the Analysis Toolpak since at least 2000. Tools -> Add-Ins ... etc

 

[TdC]

dude, my companies office is so old it very likely watched the dino's die.