OK. But the number I get is 44444. What portion of the 24 hours does that
fall into? Isn't it just as simple as that or no?
--
Thanks!
Lonnie Jo


"bpeltzer" wrote:

Now just add one and format that result as a number and you're done... the
answer is 8, because 3:55pm falls in the 8th two-hour block starting at
midnight.
Some explanation... dates in Excel are just specially formatted numbers,
where 0 is the start of the day on 1/1/1900, and every day adds 1. So when
you're looking at 3/3/2008 13:55pm, the cell's real contents are some 5-digit
number and a fractional portion that represents the time. That's why string
operations don't operate as you expect.
MyVeryOwnSelf's suggestion included formatting the date/time value in a way
to get just the hour portion ("hh"), and then performing arithmetic on that
result which you know will be an integer in the range of 0-23.

"LonnieJo" wrote:

Thank you. I copied and used this function. F2 contains 3/3/2008 3:55:00 PM
(up in the window at the top) but shows in the cell as 3/3/2008 15:55 and the
return came across as 1/7/1900 12:00:00 AM. Now what. Since I am not an
expert I am not sure what I am doing wrong. It's pretty frustrating. Love
excel, believe this is a powerful tool. UGH.
--
Thanks!
Lonnie Jo


"MyVeryOwnSelf" wrote:

I have a txt file that I have converted to xls using the wizard. In a
date/time field is data like 3/3/08 15:55. I am using the function
=right(F2,5) to extract the time only. I want to be able to count how
many incoming data in 2 hour increments over 24 hours. When I do the
function it returns 44444 in this instance for the 15:55. How can I
correct this? It's driving me crazy... LOL.. THANKS!!!!!!!


One way:
=INT(TEXT(F2,"hh")/2)
gives an integer from zero to eleven: twelve two-hour increments.

Bear in mind that dates/times in Excel are just numbers that are specially
formatted. For example, 1/1/1900 0:00 is 1.00 and 3/3/08 15:55 is
39510.6631944444.