PDA

View Full Version : IF STATEMENT TO extract result



josephm
06-13-2008, 05:40 AM
hi

i was trying to enter a date in an excel cell and then write an if statement in another cell to return what month the date was for. i tried the following statement.

=IF(A1="MM/DD/YYYY","DD","") the formula returned false.

i enter the following date in cell A1 6/13/2008

if the cell doesnt contain a date it should remain blank. if it does return the month it is.

is it possible to have the result show as the spelling of the month instead of the number

i.e if its the 6th month in the date the formula would return June.

Bob Phillips
06-13-2008, 05:41 AM
=IF(A1="","",MONTH(A1))

josephm
06-13-2008, 05:43 AM
thank you i have never seen this kind of if statement before. is there a way to have it say june instead of 6?

marshybid
06-13-2008, 06:04 AM
thank you i have never seen this kind of if statement before. is there a way to have it say june instead of 6?

You can select the individual cell(s), Column or Row containing the data and go to Format Cells, Custom, and type in mmm

This return the value as a 3 digit month (1=Jan,2=Feb, etc)

Marshybid

marshybid
06-13-2008, 06:20 AM
You can select the individual cell(s), Column or Row containing the data and go to Format Cells, Custom, and type in mmm

This return the value as a 3 digit month (1=Jan,2=Feb, etc)

Marshybid

Another way of presenting the data in "mmm" format would be to modify the fromula from xld slightly to read

=IF(A1=" "," ",TEXT(MONTH(A1),"mmm"))

If you want the full month name (January, etc) just change it to "mmmm"

This also works.

Marshybid

josephm
06-13-2008, 08:19 AM
Thank you very much.

Question

I have two sheets. Sheet1 & Sheet2.

On Sheet1 you will enter a date in cell B4 and C4 (dd/mm/yyyy)

On Sheet two cell C6 must check B4 and C4 for dates and become filled with grey.

Bob Phillips
06-13-2008, 08:20 AM
conditional form atting with a formula of

=AND(ISNUMBER(Sheet1!B4),ISNUMBER(Sheet1!C4))

josephm
06-13-2008, 08:43 AM
OK. i dont understand.

I have attached a work book with the problem.

i have entered sample information for a name.

the person wished to take their vacation form a date in week3 of january to a date in the second week in february.

since its vacation they dont count in the weekends so, the sample dates entered equals 20 days between the two dates.

so how to i get excel to shade E6:H6 which will represent the 20 days as 4 weeks.?

i have manually shaded the cells in the table on sheet2 which represent the duration of the two dates entered from sheet1.

josephm
06-13-2008, 08:54 AM
i tried to use conditional formating as suggested and it gave me a warning saying that i cannot reference different sheets