Odd situation in Excel

Closed
jeffaruni Posts 4 Registration date Monday July 1, 2013 Status Member Last seen July 3, 2013 - Jul 1, 2013 at 06:50 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jul 4, 2013 at 10:48 AM
Hello,

I'm designing a spreadsheet for my work in terms of I've got a set of numbers from 0-99, the evens are one week events and the odds are another
So odds are this week currently on the 01/07 and the evens start on 08/07
With the numbers going in sections of 20 to determine the day
E.g - 21 would be Tuesday this week
22 would be Tuesday next week
65 would Thursday

What I've got so far is a few vLOOKUP formulas to gather the information and to select what week they are on but the problem lies when it goes to the next week, it's shifting the bits round so the formula knows it should be the other week instead of the current week

I have the numbers listed and the week number they are next to them and it's listed that way but I want to find a way of it automatically changing weeks without me having to manually adjust it all.

Any help would be greatly apprecaited.

6 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 2, 2013 at 11:26 AM
Hi Jeffaruni,

So you would like to know if a number is odd or even?

If so the following functions will do so:
ISODD
or
ISEVEN

Best regards,
Trowa
0
jeffaruni Posts 4 Registration date Monday July 1, 2013 Status Member Last seen July 3, 2013
Jul 2, 2013 at 11:33 AM
Well it's more the dates, I'd like to know whether a date would come under the odd or evens and from then I want to be able to work out the next set date as the example so 21 would be today, 20 would be next Tuesday etc...
So odds are one week, evens are the other weeks so they're both bi-weekly

Thanks for the help
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 2, 2013 at 11:53 AM
Hi Jeff,

Could you post some sample data for better understanding?

This is what I got so far:
Number	Day	Week
18	Monday	18
19	Monday	19
20	Monday	20
21	Tuesday	1
22	Tuesday	2
23	Tuesday	3

Could you add the dates and show/tell which column you would like to calculate using formula?

Best regards,
Trowa
0
jeffaruni Posts 4 Registration date Monday July 1, 2013 Status Member Last seen July 3, 2013
Jul 2, 2013 at 12:10 PM
Monday 1st July - Week one
Tuesday 2nd July - Week one
Etc... Until Friday
Monday 8th July - Week two
Tuesday 9th July - week two

And then entering a number would decide what day and week number it would be
E.g 21 would be Tuesday 9th July
20 would be Tuesday 2nd July
65 would be Thursday 11th July

So entering the number would give the next date along
Its more the rolling effect, I can do it for a set week but to have it self rolling so I don't have to touch it
0

Didn't find the answer you are looking for?

Ask a question
jeffaruni Posts 4 Registration date Monday July 1, 2013 Status Member Last seen July 3, 2013
Jul 3, 2013 at 05:08 AM
That was on my phone so trying to explain was always going to be fun

Example data:

1,3,5,7,9,11,13,15,17,19 - Monday 1st July - Week one
21,23,25,27,29,31,33,35,37,39 - Tuesday 2nd July - Week one
...
81,83,85,87,89,91,93,95,97,99 - Friday 5th July - Week one
0,2,4,6,8,10,12,14,16,18 - Monday 8th July - Week two
...
82,84,86,88,90,92,94,96,98 - Friday 12th July - Week two
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jul 4, 2013 at 10:48 AM
Hi Jeff,

If you data isn't that much (like 200 rows) you could use nested IF statements.
I made a start for you:
=IF(AND(ISODD(A1),A1>0,A1<20),"Monday 1st July - Week one","")

Not sure why you would have problems with VLOOKUP. It doesn't look like it's much work to set up a data sheet for the numbers.

How far does the numbering go (100?)?

Best regards,
Trowa
0