Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
I have a spreadsheet that has the been exported from a data base into excel.
the cells have times in them as - 03:24:00 03:28:00 03:31:00 00:00:00 03:41:00 03:52:00 04:04:00 04:19:00 When I go to cell property it shows it as "general" I have close to 3000 lines as above. I need a way to calculate 03:24:00 and 04:19:00 to total 00:55 in another cell 1) How can I convert the cells to hr mm and not delete the data 2) How do I calculate the SUM of minutes to get a total amount of minutes for all the times? Any help is greatly appreciated!! Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
If I understand correctly, you have eight dates in one cell, each
separated by a single space. If you want to get these into 8 different columns you can highlight the column then do Data | Text-to-Columns and specify space as the delimiter. If you then want the difference between column H and column A, enter this formula in cell i1: =H1-A1 then copy this formula down your 3000 rows. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
Sorry Pete I should have clarified it a little better:
What I have are eight cells in one row each with A1 A2 A3 A4 A5 A6 A7 A8 03:24:00 03:28:00 03:31:00 00:00:00 03:41:00 03:52:00 04:04:00 04:19:00 These are "times" 03:24:00 is 03:24 am/0324hrs Sorry for the confusion Steve "Pete_UK" wrote in message ups.com... If I understand correctly, you have eight dates in one cell, each separated by a single space. If you want to get these into 8 different columns you can highlight the column then do Data | Text-to-Columns and specify space as the delimiter. If you then want the difference between column H and column A, enter this formula in cell i1: =H1-A1 then copy this formula down your 3000 rows. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
Another confusion now is your cell referencing - it normally goes A, B,
C etc going across ... Given that the dates are already in 8 separate cells, will my second suggestion not work now? i.e. to enter this formula in column i: =H1-A1 and copy down. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
Pete,
You are correct, very long night, yes it should have been A1,B1,C1 ect .... Yes your formula worked for about 90% of the rows .... The other 10% are when the time cross's from 23:30:00 - 00:55:00 gives me no total. Thanks for the help and sorry about the confusion. Steve "Pete_UK" wrote in message ups.com... Another confusion now is your cell referencing - it normally goes A, B, C etc going across ... Given that the dates are already in 8 separate cells, will my second suggestion not work now? i.e. to enter this formula in column i: =H1-A1 and copy down. Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
Steve, Try =IF(A1H1,H1+24-A1,H1-A1) and fill down. Neill -- neillcato ------------------------------------------------------------------------ neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750 View this thread: http://www.excelforum.com/showthread...hreadid=514500 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Cell help
As these are times, where 1 = 24hour day, you need to add 1 not 24, so
amend this formula: =IF(A1H1,H1-A1+1,H1-A1) Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capturing The First Change in a Formula | Excel Worksheet Functions | |||
Formula is entering a default time when it comes across an empty cell.. | Excel Worksheet Functions | |||
Extracting Time from a cell that has both the date and the time | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
How do I get one cell to record the time another cell was changed. | Excel Discussion (Misc queries) |