# Day Formula problem

#### Bellaby

##### Board Regular
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

James

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

##### Well-known Member
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

##### Board Regular
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?

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

##### MrExcel MVP
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

##### Well-known Member
I am still not clear but you could try something like this:
Code:
``=DAY(D\$5)+(ROWS(C\$1:C1)-WEEKDAY(DAY(D\$5),2))``
in C1 and copy it down all the way to C7.

#### Bellaby

##### Board Regular
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

##### Board Regular
I am still not clear but you could try something like this:
Code:
``=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!

##### Well-known Member
Did you try my formula. I think it always shows Monday, Tuesday and so on.

#### Bellaby

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

##### Well-known Member
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.

1,081,619
Messages
5,360,064
Members
400,566
Latest member
Usr3

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...