| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement with time values
Hello:
I'd like to implement an if statement to return a value for whether the time-of-day from a specific column is during the "day" or "night". I have one column (K) with time in the hhmm format. I've attempted the following, but it only gives me "night" as the result for all cases so something is not working correctly. =IF(AND(K3<TIME(19,0,0),K3TIME(6,59,0)),"DAY","NI GHT") Could it have to do with my time format for column K? Any help would be greatly appreciated |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement with time values
You're comparing the full date/time (Oct 2 2008 8:15 AM) to just a time (6:59
AM). If you use just the fractional portion from column K, the comparison should do what you're expecting. So where you have K3, I'd use (k3-trunc(k3)). "Dave Naples" wrote: Hello: I'd like to implement an if statement to return a value for whether the time-of-day from a specific column is during the "day" or "night". I have one column (K) with time in the hhmm format. I've attempted the following, but it only gives me "night" as the result for all cases so something is not working correctly. =IF(AND(K3<TIME(19,0,0),K3TIME(6,59,0)),"DAY","NI GHT") Could it have to do with my time format for column K? Any help would be greatly appreciated |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement with time values
Thanks. That seems to have worked perfectly.
Dave "bpeltzer" wrote: You're comparing the full date/time (Oct 2 2008 8:15 AM) to just a time (6:59 AM). If you use just the fractional portion from column K, the comparison should do what you're expecting. So where you have K3, I'd use (k3-trunc(k3)). "Dave Naples" wrote: Hello: I'd like to implement an if statement to return a value for whether the time-of-day from a specific column is during the "day" or "night". I have one column (K) with time in the hhmm format. I've attempted the following, but it only gives me "night" as the result for all cases so something is not working correctly. =IF(AND(K3<TIME(19,0,0),K3TIME(6,59,0)),"DAY","NI GHT") Could it have to do with my time format for column K? Any help would be greatly appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If statement to compare time cell to a time | Excel Worksheet Functions | |||
| using if statement to subtract 24 hours from time still shows as a negative time from both responses under 24 hours | Excel Worksheet Functions | |||
| Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
| IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
| how do i sum up a column of time values but ignore negative values | New Users to Excel | |||
Linear Mode
