IF ODD date, false, If EVEN date, ok

**yesmaybe** · 11-12-2009, 09:47 AM

In Excel 2003, I have a list of dates, which part of a train timetable. The train only departs on EVEN dates and arrives at its' destination the following day.

In A1 I have a date, 1-1-2010, for example.
In A2, I want to check the date in A1, if it is ODD, then return FALSE, if it is EVEN, then show that date +1.

What do I mean by ODD/EVEN? With 31 days in the month, 1st, 3rd, 5th .. 31st are ODD, and 2nd, 4th .. 30th are EVEN.

I have used this formula successfully, but there is an imposed limit of 7 nested arguments:
=IF(A$1=1,"FALSE",IF(A$1=2,A$1+1,IF(A$1=3,"FALSE",IF(A$1=4,A$1+1,IF(A$1=5,"FALSE",IF(A$1=6,A$1+1,IF(A$1=7,"FALSE",IF(A$1=8,A$1+1))))))))

Because of the limitations with Excel 2003 I can not grow this formula for all 31 days.

Is there a way to simplify the formula?

Many thanks for your help!
Andy

**NBVC** · 11-12-2009, 09:50 AM

Try

=IF(MOD($A1,2)=0,$A1+1,FALSE)

**yesmaybe** · 11-12-2009, 10:07 AM

Hi!

Thanks for the fast response. It seems to work, but isn't accurate. Can you explain what the formula is doing?

Your formula returns 'true' on 14-01-10, and 28-01-10 which is accurate. It returns FALSE on 21-01-10 which is also accurate, but returns FALSE on 04-02-10 which is the wrong answer. 04-02-10 is an EVEN date and so should return TRUE.

Actually, I think I may have made a mistake in my original formula and was editing it as you replied!

Currently I am using this formula below:
=OR(DAY(A$1)=2,DAY(A$1)=4,DAY(A$1)=6,DAY(A$1)=8,DAY(A$1)=10,DAY(A$1)=12,DAY(A$1)=14,DAY(A$1)=16,DAY(A$1)=18,DAY(A$1)=20,DAY(A$1)=22,DAY(A$1)=24,DAY(A$1)=26,DAY(A$1)=28,DAY(A$1)=30)

This uses the DAY argument and I think that is probably important so excel knows to read the date as a date, not as a number.

This OR formula returns TRUE if EVEN, FALSE if not (ODD). But I want to change the TRUE to a calculation and believe the OR function is limited in that way:

Does that make any sense?!
Andy

**NBVC** · 11-12-2009, 10:17 AM

Your first sample formula did not show that you are working with dates... I assumed you had separated it out....

Try

=IF(MOD(DAY($A1),2)=0,$A1+1,FALSE)

MOD() returns the remainder after a value has been divided by a divisor (2, in this case)... so if remainder is 0, then it is Even.

**yesmaybe** · 11-12-2009, 10:18 AM

I have played with your answer and added the DAY function.

=IF(MOD(DAY($A1),2)=0,$A1+1,FALSE)

That works now. Thanks for pointing me towards the MOD function.

Andy

**NBVC** · 11-12-2009, 10:33 AM

Great!

Now, if you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**yesmaybe** · 10-26-2010, 02:33 AM

It's that time of the year again, I am updating timetables and I need a recap on the Excel formulas I learned last year!

I have this formula, which checks if the day is a TUESDAY and gives IF TRUE, IF FALSE.

=IF(MOD(DAY(A1,2)=2,A1+4,A1+3)

How would I add to this, for a choice of two DAYS? So if A1 = TUES (2), +4, if A1 = SAT (6), +3, ELSE, ERROR?

Thanks!
Andy

**DonkeyOte** · 10-26-2010, 02:49 AM

Originally Posted by yesmaybe

if A1 = TUES (2), +4, if A1 = SAT (6), +3, ELSE, ERROR

Do you really want an error ?

Assuming you do...

Please Login or Register  to view this content.

that would generate an #N/A error for any other weekdays.

**yesmaybe** · 10-26-2010, 03:01 AM

thanks for the speedy reply. that works. The VLookup is a new one to me, I like it.

Actually, Excel week starts on a Sunday, so I changed it to, is that the reasoning?
=A1+VLOOKUP(WEEKDAY(A1,3),{2,4;6,3},2,0)

Can you please explain what the (A1,3) and the ,2,0) means in this code?

Thanks!
Andy

**DonkeyOte** · 10-26-2010, 03:05 AM

Originally Posted by yesmaybe

Actually, Excel week starts on a Sunday

The start of the week is determined by the (optional) return_type specified in the WEEKDAY function - the settings for which are:

1: Sunday is Day 1 (default if not specified)
2: Monday is Day 1
3: Monday is Day 0 and Sunday is Day 6

In Excel 2010 there are further options to the above

In the example posted I used a return_type of 2 (Monday is 1) ergo Tuesday is 2 and Saturday is 6.

For more help on WEEKDAY and VLOOKUP please see XL Help.

IF ODD date, false, If EVEN date, ok

LinkBack

Thread Tools

Rate This Thread

Display

IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Re: IF ODD date, false, If EVEN date, ok

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions