Day Formula problem

[Bellaby]

Hello all,

It's been a while since I last posted mainly thanks to eveyone on here helping me learn so much

However, I have a little problem that I'm not sure if there is a solution for. I'm using this formula: =DAY($D$5) which obviously returns as 0, in D5 I have the WEEKNUM formula. what I'm trying to do is make a specific day code for each week in the year stay in one cell eg. The day code for monday for any week in any year will always show up in cell C1, the day code for tuesday in C2 etc. I thought I'd try this formula: =DAY($D$5)-4 This brought up the day code for Monday, so the week number it 37 for the 14/09/2013 (09/14/2013 For our friends accross the pond).

This works great untill the week number turns to 38, and it changes the day code in cell C1 for a Tuesday. This isn't good for what I need the workbook to do.

Is there any way to make the day codes change each week but somehow arrange themselves to be in the correct cell? I.E. Monday (C1), Tuesday (C2), Wednesday (C3).... and so on.

Any help is very much appreciated

Thank you in advance
James

P.S. Macros can't be used as the workbook needs to be user friendly.

 

[Bellaby]

Can you please post a screen shot of your file? I have checked, my formula works fine for me.

Sun Mon Tue Wed Thu Fri Sat
95.00% 95.00% 95.00% 95.00% 95.00% 95.00% 95.00%
3 5
0 0 0 0 0 0 0


As you can see the days are on top in row 1 and columns C through to I. at the moment they are just text (no formula).

To go more in depth, the rows with the single numbers in rows 3 and 4 I want to capture data from another sheet in the workbook and give me a count. The other sheet has a column that has the formula =Days($B3), this gets the day for each date inputted into the sheet. this column is where rows above will be able to determine where the counts will go for that week. the problem I'm having is that Excel only searches for day codes not the word so I keep getting N/A errors.

So it's either make the days in row one change the day code each week (but stay in the same cell) or do something to the other sheet to help the formula I'm using for rows 3 and 4 to get the count.

I's a very complex workbook but unfortunately it is something that, if it works, will save a lot of paperwork in the long run.

 

[asad]

There are two solutions. Easy one is that you can put a date in each cell that you want to show as Monday - Sunday. For example you can put 16/09/2013 in C1 and format it as "dddd" and so on for rest of the days. The other solution is that change my formula to =DAY($D$5)+(COLUMNS($C1:C1)-WEEKDAY(DAY($D$5),2)) in C1 and copy across to I1.

 

[Bellaby]

I did try changing your formula but ended up with similar results plus changing the dates each week would defeat the object of what they are asking. This sheet is used all year round and they want it to grab the counts automatically.

I am trying your formula in meny different ways to see what happens but if you think of anything else I would be greatful for your imput

Thank you so much for your time.

 

[asad]

I am not sure why it is not working for you. Here is what I get with date in D4, weeknum(D4) in D5 and my formulas in C1:C7 and also in C1:I1

Mon	Tue	Wed	Thu	Fri	Sat	Sun
Tue
Wed
Thu	16/09/2013
Fri	38
Sat
Sun

<tbody>
</tbody><colgroup><col><col span="2"><col span="4"></colgroup>

 

[Bellaby]

There are two solutions. Easy one is that you can put a date in each cell that you want to show as Monday - Sunday. For example you can put 16/09/2013 in C1 and format it as "dddd" and so on for rest of the days. The other solution is that change my formula to =DAY($D$5)+(COLUMNS($C1:C1)-WEEKDAY(DAY($D$5),2)) in C1 and copy across to I1.

Just figured out what I was doing wrong with the formula!

D$5 wasn't absolute so it wasn't getting the week number for the other cells it works fine now thank you! :D

 

[bsmill2]

Why not just put Day() in one cell and Weeknum() in another and keep the days of the week static from c1 to c7? They do not change or is there something else dependent on them?