weekdays

R

RedChequer

I want to be able to give a numeric value of 0 to 6 to days entered as
[ Wed to Tue.], and have a numeric value for the entered day linked to the
entered day.
Eg. enter any day 'wed to tue' the entered day given a value of zero
If day = wed then day = 0 { then the value for fri would be 2 }
If day = fri then day = 0 { then the value for mon would be 3 }
etc.....
I have tried using the 'INDEX' function but as I have multiple day entries I
can't get it to work
 
D

duane

I listed Mon thru Sun and thru Sat again in a column and named the rang
"namelist" - 13 days listed

Placing your day "0" in cell a19 and the desired day "n" in a20, th
below give you the day # of day "n"


=IF(MATCH(A20,namelist,0)-MATCH(A19,namelist,0)<0,7+MATCH(A20,namelist,0)-MATCH(A19,namelist,0),MATCH(A20,namelist,0)-MATCH(A19,namelist,0)
 
D

Domenic

Here's another way...

On Sheet 2, enter the follow table...

A1:G7...

Wed...Thu...Fri
Thu...Fri...Sat
Fri...Sat...Sun
Sat...Sun...Mon
Sun...Mon...Tue
Mon...Tue...Wed
Tue...Wed...Thu

You'll continue this until you have seven columns.

Then, on Sheet 1...

=MATCH(B1,INDEX(Sheet2!A1:G7,0,MATCH(A1,Sheet2!A1:G1,0)),0)-1

...where A1 contains the day that equals 0 (zero) and B1 contains th
day whose value you want returned.

Hope this helps
 
D

Domenic

I forgot to mention that the days that you enter in A1 and B1 need t
match the format you use in the seven column table.

So in the example I gave you, you would have to enter the dates in A
and B1 as follows...

Mon
Tue
Wed
Thu
etc.

If you want to be able to enter the dates in full (Monday, Tuesday
etc.) in A1 and B1, then you would have to change the format of th
table accordingly
 
R

RedChequer

As I am a learner I am a bit in the dark re your reply.
If I put Mon thru Sun plus Mon thru Sun in cells A1:A13, how do I create and
what does a range named’ namelist’ refer to
I also have another query: How can I post a copy of a small spreadsheet,
16.5 KB, to the forum. ?.
 
B

Bob Phillips

Don't post spreadsheets to the forum, they will not be opened. Explain your
problem in plain text.
 
R

Ron Rosenfeld

I want to be able to give a numeric value of 0 to 6 to days entered as
[ Wed to Tue.], and have a numeric value for the entered day linked to the
entered day.
Eg. enter any day 'wed to tue' the entered day given a value of zero
If day = wed then day = 0 { then the value for fri would be 2 }
If day = fri then day = 0 { then the value for mon would be 3 }
etc.....
I have tried using the 'INDEX' function but as I have multiple day entries I
can't get it to work
Click to expand...

If your '0' day is entered in A1, then with 'any day' entered in A2, the
following formula will give you a '0' based week with '0' being the day entered
in A1:

=MOD(MATCH(A2,{"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat"},0)+7-
MATCH($A$1,{"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat"},0),7)

Note that the weekday list is an array constant (i.e. enclosed in braces, not
parentheses).

You could also put this array constant in a cell range and reference that
range.

eg: In N1:N7 enter Sun, Mon, etc. Then the formula would be:

=MOD(MATCH(A2,$N$1:$N$7,0)+7-
MATCH($A$1,$N$1:$N$7,0),7)


--ron
 
R

RedChequer

I’m afraid I can’t get your formula to work, this is not unusual for me as
I’m still a novice and haven’t quite grasped what you meant re the array data.
I renamed ‘Sheet2’ to ‘days’ ( I found it didn’t matter changing the sheet
name as Excel automatically changes it to the current altered sheet name.)
On sheet2 I entered ‘wed’ at A1 thru to ’tue’ at G1
‘thu’ at A2 thru to ’wed’ at G2
down to ‘tue’ at A7 thru to ‘mon’ at G7
In your example you said to enter dates in A1 and B1
but you had day names, mon down to sun
This is were I got confused
On sheet1 I entered ‘mon’ at A1 down to ‘sun’ at A7
Then in B1 down to B7 I tried entering dates, ‘1/6/041’ to ‘8/6/04’
(dd/mm/yy format)
as this didn’t work I tried entering numbers, ‘0’ to ‘6’
In C1 I entered the formula
=MATCH(B1,INDEX(days!A1:G7,0,.MATCH(A1,days!A1:G1,0)),0)-1
In C1 I kept getting the “no value is available†error ( #N/A )
Could you please explain where I’m going wrong?.
 
D

duane

to name the range select insert...name...define and enter the name an
then enter the range you want to have that name then select add an
your don
 
R

RedChequer

Thanks,
I'll try again
Keith


RedChequer said:
I’m afraid I can’t get your formula to work, this is not unusual for me as
I’m still a novice and haven’t quite grasped what you meant re the array data.
I renamed ‘Sheet2’ to ‘days’ ( I found it didn’t matter changing the sheet
name as Excel automatically changes it to the current altered sheet name.)
On sheet2 I entered ‘wed’ at A1 thru to ’tue’ at G1
‘thu’ at A2 thru to ’wed’ at G2
down to ‘tue’ at A7 thru to ‘mon’ at G7
In your example you said to enter dates in A1 and B1
but you had day names, mon down to sun
This is were I got confused
On sheet1 I entered ‘mon’ at A1 down to ‘sun’ at A7
Then in B1 down to B7 I tried entering dates, ‘1/6/041’ to ‘8/6/04’
(dd/mm/yy format)
as this didn’t work I tried entering numbers, ‘0’ to ‘6’
In C1 I entered the formula
=MATCH(B1,INDEX(days!A1:G7,0,.MATCH(A1,days!A1:G1,0)),0)-1
In C1 I kept getting the “no value is available†error ( #N/A )
Could you please explain where I’m going wrong?.
Click to expand...
 
D

Domenic

RedChequer said:
On sheet1 I entered ‘mon’ at A1 down to ‘sun’ at A7
Then in B1 down to B7 I tried entering dates, ‘1/6/041’ t
‘8/6/04’
(dd/mm/yy format)
as this didn’t work I tried entering numbers, ‘0’ to ‘6’
In C1 I entered the formula
=MATCH(B1,INDEX(days!A1:G7,0,.MATCH(A1,days!A1:G1,0)),0)-1
In C1 I kept getting the “no value is available� error ( #N/A )
Could you please explain where I’m going wrong?.
Click to expand...

If you're going to enter dates in Column B as you've described, the
you'll have to change the formula to...

C1, copied down:

=MATCH(TEXT(B1,"ddd"),INDEX(Sheet2!$A$1:$G$7,0,MATCH(A1,Sheet2!$A$1:$G$1,0)),0)-1

...and format Column C as "General"
 
Top