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?

 

[MyVeryOwnSelf]

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.

 

[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

 

[MyVeryOwnSelf]

... the return came across as 1/7/1900 12:00:00 AM.

Now what.

Select the cell(s) containing the formula, and try:
Format > Cells

In the dialog box,
Set the "category" to "number"
Set "Decimal places" to 0.
With this formatting, an integer from zero to eleven should be displayed,
depending on the hour of the original value: twelve two-hour increments.