Finding times between 6pm and Midnight...

[Alex]

Hello,

I have a column which contains a full date and time, for example
2007-02-03 19:01:03. I need to create another column which will just
have 1 if it's after 6PM or 0 if before. I created a column called
Hour and using a Custom Format with just 'h' now have the hour in 24
hour format. I then created an if statement with this:
=IF(R2>17,1,0)

R2 is where the Hour field is. Problem is every field is 1. How can
I calculate the hour in a date field? Obviously the custom format is
only for display, and all calculations are still looking at the full
date. I'm using MS SQL 2007, but I assume any solution would work on
prior versions of Excel.

Thanks --

Alex

 

[gordon.moar]

I have a column which contains a full date and time, for example
2007-02-03 19:01:03. I need to create another column which will just
have 1 if it's after 6PM or 0 if before. I created a column called
Hour and using a Custom Format with just 'h' now have the hour in 24
hour format. I then created an if statement with this:
=IF(R2>17,1,0)

R2 is where the Hour field is. Problem is every field is 1. How can
I calculate the hour in a date field? Obviously the custom format is
only for display, and all calculations are still looking at the full
date. I'm using MS SQL 2007, but I assume any solution would work on
prior versions of Excel.

Rather than create an extra column, I would use something like

=IF(MOD(dateAndTime)>0.75,1,0)

since 0.75 is 1/24*18 i.e. the fractional part of the date/time value
that equates to 6 o'clock.

 

[NickHK]

Alex,
Depending on what you actually want, you either have too much info or not
enough to make you comparison.
Formatting a cells does not change its underlying value and as dates are
stored as doubles, you are currently comparing a large number like 39000 to
17, so you will get all 1s.
?cdbl(cdate("2007-02-03 19:01:03"))
39116.7923958333

The decimal portion of this number represents the time. So it seems that you
need to compare that part to 18/24, to see if the time is before or after 6
p.m. on the day in question.

Of course 01:05 a.m. is still after 6 p.m.the day before, in which case you
would compare the date with the Date value of the next day 6 p.m.

NickHK