|
Expert in Excel Needed.... HELP
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!!!!!!! -- Thanks! Lonnie Jo |
Expert in Excel Needed.... HELP
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" 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!!!!!!! -- Thanks! Lonnie Jo |
Expert in Excel Needed.... HELP
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? -- Thanks! Lonnie Jo "FSt1" wrote: 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" 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!!!!!!! -- Thanks! Lonnie Jo |
Expert in Excel Needed.... HELP
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. |
Expert in Excel Needed.... HELP
|
Expert in Excel Needed.... HELP
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. |
Expert in Excel Needed.... HELP
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. |
Expert in Excel Needed.... HELP
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. |
Expert in Excel Needed.... HELP
check mail. you'll have your answer to where 44444 came from.
regards FSt1 "LonnieJo" wrote: 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. |
Expert in Excel Needed.... HELP
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 On Jul 10, 2:51*am, LonnieJo 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!!!!!!! -- Thanks! Lonnie Jo |
Expert in Excel Needed.... HELP
... 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. |
| All times are GMT +1. The time now is 02:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com