Odd situation in ExcelClosed

Question

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.

TrowaD · Answer

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

jeffaruni · Answer

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

TrowaD · Answer

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

jeffaruni · Answer

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

jeffaruni · Answer

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

TrowaD · Answer

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

Odd situation in Excel

6 responses

Similar discussions

Subscribe To Our Newsletter!