Adding days

mtagliaferri

Board Regular
Joined
Oct 27, 2004
Messages
120
Hi all, I have this proble I would like to solve: in A1, A2, A3 ... the week days in the following format MONDAY, TUESDAY WEDNESDAY..... I B1 I have the number of days of lenght as 2, B2 as 5....I would like as result in C1 to be A1+B1 = TUE in C2 the result as SAT.
To make it more clear:

MONDAY + 2DAYS = TUE
TUESDAY + 5DAYS = SAT

Is this possible????
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Perhaps this formula in C2 copied across

=IF(OR(A1="",A2=""),"",TEXT(MATCH(LEFT(A1,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)+A2,"ddd"))


....if you need the day to be in upper case change to

=IF(OR(A1="",A2=""),"",UPPER(TEXT(MATCH(LEFT(A1,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)+A2,"ddd")))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
mtagliaferri

Is this any use?

1. List of days in E1:E7
2. Formula in C1 (copied down):
=INDEX($E$1:$E$7,MOD(MATCH(A1,$E$1:$E$7,0)+B1-2,7)+1)
Mr Excel.xls
ABCDEF
1MONDAY2TUESDAYMONDAY
2TUESDAY5SATURDAYTUESDAY
3SATURDAY2SUNDAYWEDNESDAY
4SATURDAY3MONDAYTHURSDAY
5WEDNESDAY5SUNDAYFRIDAY
6MONDAY7SUNDAYSATURDAY
7MONDAY8MONDAYSUNDAY
8FRIDAY4MONDAY
9
Add Days
 

mtagliaferri

Board Regular
Joined
Oct 27, 2004
Messages
120
Thanx for the quick reply, I have tried both suggestions but I do have a error, I have tried the second sample on a clear new sheet and I have a error highlighting $E$7,MOD
Any clue why it is not working :(
Thnx
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
Thanx for the quick reply, I have tried both suggestions but I do have a error, I have tried the second sample on a clear new sheet and I have a error highlighting $E$7,MOD
Any clue why it is not working :(
Thnx
Are you using a standard English version of Excel?
Can you copy and paste your formula to the board?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Did you have a problem with my suggestion? Here's an illustration
Book1
ABCD
1TuesdayMondayWednesday
25270
3SATTUETUE
4
Sheet1


As Peter says, if you have a non-English version of excel you may have to modify the formula
 

Watch MrExcel Video

Forum statistics

Threads
1,111,908
Messages
5,541,531
Members
410,547
Latest member
htran4
Top