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.

 

[asad]

Just to make it clear, do you want the date of Monday in C1 for the week number in D5 or you just want C1 to show Monday?

 

[Bellaby]

Just to make it clear, do you want the date of Monday in C1 for the week number in D5 or you just want C1 to show Monday?

Hi asad,

The date code will update its self I just want the day within the week to stay in a particular cell (Monday C1).

At the moment when I change the date to a week ahead, this then changes the week number which in turn change all the days in cells C1, D1, E1, F1, G1, H1, I1. So it messes up the sheet and there is no consistancy.

 

[Rick Rothstein]

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.

I am not completely clear on your set up, but if D5 contains a date, then the following formula should return the date for the Monday of the week D5 occurs in...

=A1-WEEKDAY(A1)+2

 

[asad]

I am still not clear but you could try something like this:

=DAY(D$5)+(ROWS(C$1:C1)-WEEKDAY(DAY(D$5),2))

in C1 and copy it down all the way to C7.

 

[Bellaby]

Ah right, no, the date can be anywhere, so for now have the date in D4. In D5 the formula is =WEEKNUM(D4,1) then in C1 I have =Day(D5)+2. So if the date is 14/09/2013 D5 will return the result 37 then C1will return the Day code for Monday.

This is great untill the week changes,if you change the date to the 16/09/2013 The day code in C1 then changes to Tuesday.

I'm basically trying to find a way to keep the days in the same cell even when the day code changes.

I apologise about how complicated this is.

 

[Bellaby]

I am still not clear but you could try something like this:

=DAY(D$5)+(ROWS(C$1:C1)-WEEKDAY(DAY(D$5),2))

in C1 and copy it down all the way to C7.

Ok I'll give it a go

Thank you!

 

[asad]

Did you try my formula. I think it always shows Monday, Tuesday and so on.

 

[Bellaby]

I hav tried it but it only shows monday three times then ###### in the other cells

 

[asad]

I hav tried it but it only shows monday three times then ###### in the other cells

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