Excel and formula for time (to save me lots of it !!)

[john2412]

I am using times to calculate ratings for my purpose and wonder if anyone can help me with the formulas I need for the following examples which I assume are slightly different :


These first two I assume are the same............
Cell contains time in format 1:45.79 which is 1 minutes 45.79 seconds I want in another cell to calculate the exact number ie 105.79 seconds so I can then divide that answer by, for example, 3 and get 35.26

Cell contains time in format 4:02.84 which is 4 minutes 2.84 seconds I want in another cell to calculate the exact number ie 242.84 seconds so I can then divide that answer by, for example, 5 and get 48.57


And these would be different .........................
Cell contains time in format 15:47.15 which is 15 hours 47 minutes and 15 seconds I want in another cell to calculate the exact number so I can then divide that answer by, for example, 12 and get 1:31.55.

Would this formula be different if it read 15:47.15.75 being 15 hours 47 minutes and 15.75 seconds ?


many thanks

John

[Pete_UK]

Time is stored internally in Excel as fractions of a 24-hour day, so 12:00:00 is stored as 0.5, 6:00am is 0.25, 6:00pm is 0.75, and so on. You can convert times into (decimal) seconds like this:

=A1 * 24 * 60 * 60

Format the cell as General. Your format for hours minutes and seconds is different from normal (15:47:15) and can be very confusing as 15:47.15 could be interpreted as 15 minutes and 47.15 seconds (are you sure that it isn't that value anyway?). If it was in the normal format then the above formula would still apply.

Hope this helps.

Pete

[Chris 53]

Dates and times in Excel are stored as a number. Integer's being days and hours and minutes being decimal places.

This being the case you can safely divide a time by a number and be given a decimal number. All you have to do is make sure that the cell is formatted to show the decimal number as hh:mm:ss.00

[john2412]

Thanks Pete

That works perfectly for the first two examples and in itself will save me a lot of time.

The second example is indeed as I stated of hours minutes seconds and decimal seconds which I need to work with too

The times are all race times and the latter are longer race times - and all have come from pdf files which I have converted straight in to excel to work with. Having done the division I then have to get it back to the hours minutes seconds etc format.

John

[Chris 53]

The format you want to use is hh:mm:ss.00

[Pete_UK]

John,

I think you need to post an example workbook, then we can see what you are working with.

Pete

[john2412]

The format you want to use is hh:mm:ss.00

Thanks Chris - do you mean the source cell as I assume rather than the formula cell ? And what would the formula actually need to be considering the original formula.........

=A1 * 24 * 60 * 60 * ????

thanks
John

[Chris 53]

Hi John

Assuming the source cell is A1 and the formula cell is B1 then format B1 as hh:mm:ss.00 and use the formula =A1/12

Chris

[john2412]

Pete

The first formula is fine for all I am doing in my current workbook but on one I haven't yet set up I know I have some times in that format which is why I was trying to clarify on that too........... will post in as examples :

5 1:38:59.78 Annabel Orjuela
6 1:39:19.80 Erin Gray
7 1:40:38.14 Miranda Melville
8 1:42:22.15 Angela Castro
9 1:42:26.94 Katie Burnett

In order to do what I need to for a complete job I need to work in hours, minutes, seconds (plus part seconds) as well as just hours minutes seconds OR minutes seconds and part seconds

thanks
John

[Chris 53]

Hi John

I think that you haven't picked up what Pete and I are trying to tell you.

Excell stores dates and times as a simple number.

So if you put into a cell the formula =now() you will get a result of 13/08/2013, if the cell is formatted as a date. Format the cell as dd/mm/yyyy hh:mm and it displays 13/08/2013 12:09, if you format it as hh:mm:ss.00 then it displays 12:09:44.83. If you format the cell as a number with lots of decimal places you get 41499.5095058. That is 41499 days since 01/01/1900 and being 0.5095058 of a 24 hour day, ie 12:09.

It's all the same number as far as excel is concerned, its just how you want the number to be displayed to those of us who don't recognise 41499.5095058 as today just after 12:09.

This means that as far as excel is concerned you can divide it, multiply it add a whole number or a decimal number and just change the format to display what you want to see.

Hope this helps

[john2412]

Pete and Chris

Got BOTH of the types of times working fine in my spreadsheet now - hadn't realised that as I had dropped the worksheet out from pdf to excel I had to format BOTH source and target cells with hours minutes seconds and decimal seconds ie making it in to a 1:42:26.94 and not 1:42.26.94 as it was showing from the conversion. ( Without the hours "complication" it seemed to convert and format ok for the simpler first example )

That will save me many hours of manual conversion work - thank you very much

regards
John

[Pete_UK]

John,

the examples that you posted in post #9 are in the normal format of hh:mm:ss.nn, so the formula I gave you will work on those to produce seconds (and fractions thereof). Just format the cell with the formula in as General, or as Number with 2 dp.

Hope this helps.

Pete