# Dum IF statement question!



## jackmccarthy (Feb 1, 2013)

Hello
I have a calculated column in powerpivot that has numbers 0 thur 24 called "hour of day" That represent the hours of the day.
I need to say if the hour is 0 to 8 "GRAVE" 8 TO 16 "DAY" AND 16 TO 24 "SWING". Can someone please give me the =if statement for this?

This is what I have. Not working  
=IF([hour of day]<8,"GRAVE",IF([hour of day]<16,"DAY","SWING"))THANKS </pre>


----------



## MD610 (Feb 1, 2013)

What about...
=SWITCH(TRUE(), [hour of day]<=8, "GRAVE", [hour of day]>8 && [hour of day]<=16, "DAY", "SWING")

Or just replace the argument for your second IF statment with this one: [hour of day]>8 && [hour of day]<=16

You might have to play with the <=, <, >, and >= depending on where you need to group the 8 and 16.


----------



## jackmccarthy (Feb 1, 2013)

Gives me error to many if arguments were passed


----------



## MD610 (Feb 1, 2013)

You are missing or have misplaced a comma or parenthesis somewhere.


----------



## MD610 (Feb 1, 2013)

=IF([hour of day]<8,"GRAVE",IF([hour of day]>8 && [hour of day]<=16,"DAY","SWING"))

This or the SWITCH() should give the same result...aside from the variations in the <, <= etc. that you need to adjust depending on where you want the 8 and 16.


----------



## jackmccarthy (Feb 1, 2013)

This is what I have =IF([hour of day]<8,"GRAVE",IF([hour of day]>8 && [hour of day]<=16 ))


----------



## MD610 (Feb 1, 2013)

It needs to be this:
=IF([hour of day]<=8,"GRAVE",IF([hour of day]>8 && [hour of day]<=16,"DAY","SWING"))

This will make the 8's part of GRAVE and the 16's part of DAY.


----------



## jackmccarthy (Feb 1, 2013)

=IF([hour of day]<8,"GRAVE",IF([hour of day]>8 && [hour of day]<=16,"DAY","SWING") ok now I am returning swing in all rows of column


----------



## MD610 (Feb 1, 2013)

Is your table filtered to just show SWING somewhere?


----------



## jackmccarthy (Feb 1, 2013)

no????????? ready to throw this thing accross the room...lol


----------



## jackmccarthy (Feb 1, 2013)

Hello
I have a calculated column in powerpivot that has numbers 0 thur 24 called "hour of day" That represent the hours of the day.
I need to say if the hour is 0 to 8 "GRAVE" 8 TO 16 "DAY" AND 16 TO 24 "SWING". Can someone please give me the =if statement for this?

This is what I have. Not working  
=IF([hour of day]<8,"GRAVE",IF([hour of day]<16,"DAY","SWING"))THANKS </pre>


----------



## powerpivotpro (Feb 1, 2013)

Is [hour of day] column a numeric data type or text?  just swinging in the dark here.  send me the workbook!


----------



## jackmccarthy (Feb 4, 2013)

ok Rob I would love to email it to you. What's the address?


----------



## powerpivotpro (Feb 4, 2013)

It's spelled out in my signature above to avoid spam bots


----------



## jackmccarthy (Feb 4, 2013)

Any luck yet?


----------



## powerpivotpro (Feb 4, 2013)

Hrm.  I have not received the file yet.  Can you try robert.c.collie?  That's an email account on gmail.


----------



## Laurent C (Feb 6, 2013)

Is [hour of day] a column, a calculated column or a measure?


----------



## jackmccarthy (Feb 6, 2013)

Hour of day is a calculated column formatted of of datetime stamp column


----------



## Laurent C (Feb 6, 2013)

OK. Can you give the definition of the calculated column? My guess is that you compare numbers with dates and that causes the issue.


----------



## jackmccarthy (Feb 6, 2013)

Ok ,Yes I have a powerpivot table called "transactions" in that table there is a column called [datetimestamp] its info is the date and time of day. I then added a new column formatted it to just return the hour 00 thur 24 [hour of day] then added another column
where the if statement is.


----------



## Laurent C (Feb 7, 2013)

Sorry if was not clear about that: I was interested in knowing what the formula for [hour of day] is.


----------



## jackmccarthy (Feb 1, 2013)

Hello
I have a calculated column in powerpivot that has numbers 0 thur 24 called "hour of day" That represent the hours of the day.
I need to say if the hour is 0 to 8 "GRAVE" 8 TO 16 "DAY" AND 16 TO 24 "SWING". Can someone please give me the =if statement for this?

This is what I have. Not working  
=IF([hour of day]<8,"GRAVE",IF([hour of day]<16,"DAY","SWING"))THANKS </pre>


----------



## jackmccarthy (Feb 7, 2013)

[hour of day] is [datetimestamp],"HH"  The [datetimestamp] is 9/24/2012 12:50:58 pm Hope that is what your looking for.


----------



## MD610 (Feb 7, 2013)

Try using =HOUR([datetimestamp]) instead for [hour of day].


----------



## Laurent C (Feb 7, 2013)

MD610 is right on point.

Explanation: formatting a date has no impact on its value. When compared to a numeric value, your date (9/24/2012 12:50:58 pm) will be casted to a numeric value as well (41176.53... in that case). For any recent dates this number will always be larger than 16.


----------



## jackmccarthy (Feb 7, 2013)

that work perfectly Thanks!!!!!!  please see other post from today. If anyone can help there I will by you all drinks!!!!


----------

