Multiple If statement, else statement

[iluvsafc]

Hi Guys,

I'm usually OK with if statements but when it comes to multiple criteria i tend to get a bit confused.

Basically when a date is inputted that falls between the below date range i want the output in the next column to be the academic year.

Hope that makes sense guys!

Cheers,
Lee

Date range Academic Year <tbody> </tbody> 01/08/2012 31/07/2013 <tbody> </tbody> 2012/13 <tbody> </tbody> 01/08/2013 <tbody> </tbody> 31/07/2014 <tbody> </tbody> 2013/14 <tbody> </tbody> 01/08/2014 <tbody> </tbody> 31/07/2015 <tbody> </tbody> 2014/15 <tbody> </tbody> 01/08/2015 <tbody> </tbody> 31/07/2016 <tbody> </tbody> 2015/16 <tbody> </tbody> 01/08/2016 <tbody> </tbody> 31/07/2017 <tbody> </tbody> 2016/17 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[gallen]

Assuming your inputted date is in Cell A1. In Cell B1 put:

=IF(MONTH(A1)>7,YEAR(A1) & "/" & YEAR(A1)+1,YEAR(A1)-1 & "/" & YEAR(A1))

 

[iluvsafc]

Assuming your inputted date is in Cell A1. In Cell B1 put:

=IF(MONTH(A1)>7,YEAR(A1) & "/" & YEAR(A1)+1,YEAR(A1)-1 & "/" & YEAR(A1))

You're a star!! Thanks very much

 

[Muhammad_Usman]

Assum your date is in Cell C3

=RIGHT(YEAR(C3),4)&"/"&RIGHT(YEAR(C3),2)+1

 

[gallen]

You're a star!! Thanks very much

No problem, I'm good with the straight forward ones

 

[gallen]

Assum your date is in Cell C3

=RIGHT(YEAR(C3),4)&"/"&RIGHT(YEAR(C3),2)+1

That won't work for dates before 1/8. You need the if statement

 

[iluvsafc]

Sorry one more thing, can you stick an 'if blank' in there for me please with an output of "Provide start date"

Cheers!

 

[Muhammad_Usman]

=IF(A1="","Provide start date",IF(MONTH(A1)>7,YEAR(A1)&"/"&YEAR(A1)+1,YEAR(A1)-1&"/"&YEAR(A1)))

 

[gallen]

Sorry one more thing, can you stick an 'if blank' in there for me please with an output of "Provide start date"

Cheers!

This checks if the value is at least numeric. There is no built in function to check if the user has put a valid date but at least this is a little more than just checking for a blank cell

=IF(ISNUMBER(A1), IF(MONTH(A1)>7,YEAR(A1) & "/" & YEAR(A1)+1,YEAR(A1)-1 & "/" & YEAR(A1)),"Please Enter a Valid Date")

 

[gallen]

If you are familiar with the code window you could insert your own function. If you go to the code window (Alt + [F11]) then go to the Project explorer (Ctrl + R) > Right Click the 'Microsoft Excel Objects' folder and choose Insert>Module.

In the module window paste:

Public Function IsDateValue(r As Range) As Boolean
    IsDateValue = IsDate(r)
End Function

Then you can use

=IF(IsDateValue(A1), IF(MONTH(A1)>7,YEAR(A1) & "/" & YEAR(A1)+1,YEAR(A1)-1 & "/" & YEAR(A1)),"Please Enter a Valid Date")

This will only accept a valid date