Day Formula problem

Bellaby

Board Regular
Joined
Nov 28, 2011
Messages
78
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

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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
Joined
Nov 28, 2011
Messages
78
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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
Joined
Nov 28, 2011
Messages
78
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
Joined
Nov 28, 2011
Messages
78
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!
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
Did you try my formula. I think it always shows Monday, Tuesday and so on.
 

Bellaby

Board Regular
Joined
Nov 28, 2011
Messages
78
I hav tried it but it only shows monday three times then ###### in the other cells :(
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
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.
 

Forum statistics

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

Some videos you may like

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...
Top