Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
i have read and tried all of the threads on calculating time difference but
none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
Siteman wrote:
i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? =ROUNDUP((B1-A1)*24,0) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
Thanks for your quick response. this did not work, it came back with an error in value message. I am wondering if it has to do with the am and pm that is in the cell with the times. also do i have each cel formatted properly? the cells have this exaclty start end hours 11:10am 01:00pm i have the time cells formatted in custom, with the following Time cells h:mm AM/PM and the total hours, formated as ,, number -123.10 any other ideas? "Glenn" wrote: Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? =ROUNDUP((B1-A1)*24,0) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
Remember that the format, if you wish to use AM/PM, does include a space
before the AM or PM. It is 2:00 PM, not 2:00PM. It is no good just formatting the *display* of the cell if you haven't entered it correctly, as an incorrectly entered string will be treated as a text string, and cell date formatting has no effect on text strings. If you've got a vast amount of data incorrectly entered, you can use Edit/ Replace to change AM without a space to AM with a space at the beginning of the string, & similarly for PM. -- David Biddulph Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
It looks like it defineatly has to do with the export having the am/pm in the
number cell, if i find all am and pm and replace with nothing, in other words remove the refernce, then excel automatically puts in caps AM - which of course does not work either. I suspect now it has to be on a 24 hour clock so that excell can figure that out, i will try this let you know how it works out. hopefully the software that i am exporting from has this option. "Glenn" wrote: Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? =ROUNDUP((B1-A1)*24,0) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
Siteman wrote:
It looks like it defineatly has to do with the export having the am/pm in the number cell, if i find all am and pm and replace with nothing, in other words remove the refernce, then excel automatically puts in caps AM - which of course does not work either. I suspect now it has to be on a 24 hour clock so that excell can figure that out, i will try this let you know how it works out. hopefully the software that i am exporting from has this option. See David Biddulph's response. Basically, replace "AM" with " AM" and replace "PM" with " PM". Then see if it works for you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
yes this is indeed the problem, there is no way for me to convert to a 24
hour clock in the exporting software, it exports times like this 01:00pm which would be 13:00 on the 24 hour clock. when i format the cells to h:mm AM/PM, and then remove the exported am/pm from the cells, it converts it all to am, as it was exported on a 12 hr clock. Is there a way to convert anything that has a pm at the end of it to a 24 hour clock without doing a macro for each time. in other words, can i have all of my 12 hour clock times in the pm converted to a 24 hour clock with some kind of formula to seek and convert? 3:50pm = 15:50pm? every number from 01:00 - 11:00pm = add 12? just trying to find a way to convert the data, so that excell can work in the 24 hour clock. and so i dont have to week through a dozen or so macros "Glenn" wrote: Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? =ROUNDUP((B1-A1)*24,0) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
ALmost there
by converting to a 24 hour clock then inserting the space as you laid out below, it works, however it is not calculating all cells, using this formula here is the results =ROUNDUP((C3-B3)*24,0) any number in the 12 hour clock works, but in the 24 our clock it has an error in value, for example 9:00 am - 10:50 am = 2 (hours) good 11:10 am -13:00 pm = error ( should be 2 (hrs) ) i notice something else, the cells containing pm are not calculated again referring to the 12 clock ideas? "David Biddulph" wrote: Remember that the format, if you wish to use AM/PM, does include a space before the AM or PM. It is 2:00 PM, not 2:00PM. It is no good just formatting the *display* of the cell if you haven't entered it correctly, as an incorrectly entered string will be treated as a text string, and cell date formatting has no effect on text strings. If you've got a vast amount of data incorrectly entered, you can use Edit/ Replace to change AM without a space to AM with a space at the beginning of the string, & similarly for PM. -- David Biddulph Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time calculation
got it
the mistake i made was converting to the 24 hour clock, all i needed to do was add the spaces and the formula thanks everyone! "Siteman" wrote: ALmost there by converting to a 24 hour clock then inserting the space as you laid out below, it works, however it is not calculating all cells, using this formula here is the results =ROUNDUP((C3-B3)*24,0) any number in the 12 hour clock works, but in the 24 our clock it has an error in value, for example 9:00 am - 10:50 am = 2 (hours) good 11:10 am -13:00 pm = error ( should be 2 (hrs) ) i notice something else, the cells containing pm are not calculated again referring to the 12 clock ideas? "David Biddulph" wrote: Remember that the format, if you wish to use AM/PM, does include a space before the AM or PM. It is 2:00 PM, not 2:00PM. It is no good just formatting the *display* of the cell if you haven't entered it correctly, as an incorrectly entered string will be treated as a text string, and cell date formatting has no effect on text strings. If you've got a vast amount of data incorrectly entered, you can use Edit/ Replace to change AM without a space to AM with a space at the beginning of the string, & similarly for PM. -- David Biddulph Siteman wrote: i have read and tried all of the threads on calculating time difference but none of them work. Specifically i have done an export of data to excell the times come out like this, A B C start end 2:00pm 3:50pm 1 ( i want to round this to the nearest hour) I formated the above cells h:mm pm and the formula cell as general with decimals i have tried =b1-a1 =int((b1-a1)*24) none of which work, if i delete the pm or am it will calculate, but not accurately. I want to round up the time calculation to the nearest number, ie, 2:00pm 3:50pm 2(hours) any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop time - start time calculation | Excel Worksheet Functions | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
Please help with time calculation | Excel Discussion (Misc queries) | |||
time calculation with military time | Excel Worksheet Functions |