Days of the Week On Excel

Question

I am trying to do something with the days of the week. I have a cell on Sheet1 called 'Yr Overview' where a user inputs the day of the week.

On a second page I am reference thing by using =left('Yr Overview'!A1,1) so that is a user inputs a day like Sunday, if just shows it as an S. I have it working up to this point. Now I am trying to make that cell effect others on the same sheet.

So that is A1 = S A2 = M (S + 1 day) A3 = T (M + 1 day) etc.. I seem to be unable to do this, so I am just asking if this is possible? Or would I need to use VBA to achieve this.

Answer 1

In A2 add this formula:

=IF(ROW()<8,IF(FIND(LOWER(LEFT(Sheet1!$A$1,3)),"montuewedthufrisatsun",1)+(ROW()-1)*3<=LEN("montuewedthufrisatsun"),UPPER(LEFT(MID("montuewedthufrisatsun",FIND(LOWER(LEFT(Sheet1!$A$1,3)),"montuewedthufrisatsun",1)+(ROW()-1)*3,3),1)),UPPER(LEFT(MID("montuewedthufrisatsun",FIND(LOWER(LEFT(Sheet1!$A$1,3)),"montuewedthufrisatsun",1)-(ROW()*-3+24),3),1))),OFFSET(A2,-7,0))

Assumes day is input in cell A1 on Yr Overview

Answer 2

Below solution helps only if in case of input date instead of day.

A1- 07/14/2017
A2=TEXT(A1+1,"dddd")
A3=TEXT(A1+2,"dddd")
A4=TEXT(A1+3,"dddd")
A5=TEXT(A1+4,"dddd")
A6=TEXT(A1+5,"dddd")
A7=TEXT(A1+6,"dddd")

Answer 3

Try this out. Enter this formula in cell A1 and drag to the right.

=LEFT(TEXT(MATCH('Yr Overview'!$A$1,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)+COLUMN()-1,"dddd"))

Answer 4

Try this in A2 and copy down:

=INDEX({"MON","TUE","WED","THU","FRI","SAT","SUN"},MATCH(UPPER(LEFT(A1,3)),{"SUN","MON","TUE","WED","THU","FRI","SAT"},0))