Expert in Excel Needed.... HELP

[LonnieJo]

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!!!!!!!

 

[FSt1]

hi
i think you have run into a format problem. reformat your date/time to
general and you will see what i mean. with formating, what you see on the
screen may not be what's in the cell. fomating does not change data, it just
changes the way it looks.
try a fomula like this....
=F2-DATE(YEAR(F2),MONTH(F2),DAY(F2))
you will get something like
1/1/1900 15:55 so change the way it looks..uh...reformat to hh:mm.

regards
FSt1

 

[LonnieJo]

Thanks but changing the formatting didn't help. Using the new formula didn't
help either. Is there somewhere I can send you a sample sheet so you can see
what is happening?

 

[FSt1]

hi
(e-mail address removed)

regards
FSt1

Thanks but changing the formatting didn't help. Using the new formula didn't
help either. Is there somewhere I can send you a sample sheet so you can see
what is happening?

 

[LonnieJo]

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.

 

[bpeltzer]

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]

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?

 

[FSt1]

check mail. you'll have your answer to where 44444 came from.
regards
FSt1

 

[muddan madhu]

suppose u have data in A1 3/3/08 15:55 , In B1 put =A1
Format cell for B1 | ctrl + 1 | format cell | category: Time | type :
HH:MM | ok