How can I convert a value in the form of HH:MM:SS.111 to just seconds?

[benno64]

Hi everyone, this is my first time in a forum so I apologise if Ive already done something wrong!

Heres what I'm trying to do:

I have a value of 0:01:20.555 in a cell in the form of hh.mm.ss.111 and I want to get the adjacent cell to display that value in just seconds (ss.11 to be precise).

So say for example I used 0:01:20.555 (1 minute and 20.555 seconds). So the 1 minute is 60 seconds, so overall the time in seconds would be 80.55 (I need to round off to 2 decimal places instead of the previous 3).

Examples:
0:01:20.555 becomes 80.55.
01:00:00.000 becomes 3600.
0:02:01.111 becomes 121.11

Does anybody know how this can be done for a whole set of data? Many thanks guys!

[FDibbins]

Try this, formatted as general...
=A1*60*60*24

[shg]

Or just change the format of the existing numbers:

	A	B	C	D
1	01:20.555	80.555		B1: =A1
2	00:00.000	3600.000		Format of A1: mm:ss.000
3	02:01.111	121.111		Format of B1: [s].000

[benno64]

Or just change the format of the existing numbers:

	A	B	C	D
1	01:20.555	80.555		B1: =A1
2	00:00.000	3600.000		Format of A1: mm:ss.000
3	02:01.111	121.111		Format of B1: [s].000

Hi,

That doesn't seem to have worked... I'm not sure if I explained myself well enough. I want excel to do the conversion for me. So I input the time as hh:mm:ss.111 and then I want it to give me it in just ss.11 format.

I think from what you have said I would have to manually have to input the ss.11 figures which is what I am trying to get excel to do for me.

Any other thoughts?

[alvin-chung]

Hi benno, formatting suggested by shg works for me.
Could you share more details as in how it doesn't worked for you?

This are the steps that I've taken as suggested by shg:
1. A1=0:01:20.555
A2=01:00:00.000
A3=0:02:01.111
2. Create formula in column B to reference column A, i.e. B1=A1, B2=A2, B3=A3
3. Format column B with custom type [s].00


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[benno64]

Hi benno, formatting suggested by shg works for me.
Could you share more details as in how it doesn't worked for you?

This are the steps that I've taken as suggested by shg:
1. A1=0:01:20.555
A2=01:00:00.000
A3=0:02:01.111
2. Create formula in column B to reference column A, i.e. B1=A1, B2=A2, B3=A3
3. Format column B with custom type [s].00


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi alvin-chung,

Ive included some screenshots of the steps that you and shg gave me and the outcome, as I don't understand why its not working...
1.png2.jpg3.jpg4.jpg

As you can see the values in B1, B2 & B3 are wrong...

Thanks

[benno64]

As well Im still trying another work around, and if it works ill post my solution. But it consists of me splitting up the hh:mm:ss.111 splitting them up into separate columns using the "Text to column" tool.

However the "Delimitated" function is not separating the hh:mm:ss.111 into columns. I suspect because it can not see the colons. The colons are being placed in the cell due how the cell is being formatted and have not actually been typed in to the fx box i.e. colons are not displayed in the top fx box, so 15:30:10.111 in a cell displays in the top fx box as just 153010111 so the delimitated function can not separate as it can not see the colons.

So I am having to use the "Fixed Width" tool instead. But I am encountering a problem as 0:00:00.111 in the fx box is shown as 111 and not 00000111 (I need it to stay as 00000111 in the fx box and not 111) .

So how can i get excel to recognise and keep zeroes, instead of deleting the zeros from the fx box if a figure starts with a bunch of zeros?

[alvin-chung]

Hi benno64, thanks for the screenshots.
Could you capture again 3.jpg without modifying the formatting so we can have a look at the original formatting of your B1 after entering formula B1=A1?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[bebo021999]

I guess that 00:01:20.555, by Text To Column, to become 120555, formated as [s].000, showed as 120555*86400=10415952000, as displayed in 3.jpg.

[FDibbins]

@ alvin, please dont ask for pics, sample workbooks are what we need to work with

@ benno, Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
Also, did you see post #2?

[alvin-chung]

@ alvin, please dont ask for pics, sample workbooks are what we need to work with

Noted

Hi benno64, could you try saving/closing all excels on your machine; then open a new workbook and try again?
If you still couldn't get the intended result, try post sample of your work and members can help take a look.


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[benno64]

@ FDibbins yes I saw post 2, it didn't help. I just did gave me 10415952000. As it did 0:01:20.555 * 24*60*60. So it read it is 120555*24*60*60. Whereas what I want it to do is know that ":01" is 1 minute and now that that equates to 60 seconds and then adds the 20.555 to give 80.555 which is what I want it to show is.

You say you want a sample workbook. Do you just want me attach my excel document?

Thanks for the help and forum tips

[alvin-chung]

Hi benno64, attaching your excel document would be fine (remember to remove any personal/sensitive information).
You might also want to give it a try on a new workbook? (I.e. save/close all workbooks and try shg method on a new one)


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[FDibbins]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[benno64]

Here is my workbook... Fingers crossed you guys can succeed where I have (somewhat) failed!

Workbook1.xlsx

[FDibbins]

It makes it so much easier when you can see the data you are working with

You did not say that your "time" was actually just a 9/8 didit regular number, that was hysr custom formatted to look like time.

Try this, copied down, and format as NUMBER...
=(TIME(LEFT(B2,IF(LEN(B2)=9,2,1)),MID(B2,IF(LEN(B2)=9,3,2),2),MID(B2,IF(LEN(B2)=9,5,4),2)&"."&RIGHT(B2,3))-TIME(LEFT(A2,IF(LEN(A2)=9,2,1)),MID(A2,IF(LEN(A2)=9,3,2),2),MID(A2,IF(LEN(A2)=9,5,4),2)&"."&RIGHT(A2,3)))*60*60*24

Note that time is actually a decimal value of 1 (day), so 06:00 am is 0.25, 12 noon is 0.5 etc. Because of that, and the fact that ecxel only only works to 15 decimal places, the "decimal" seconds are not taken into account here...

	A	B	C	D	E
1	Start Time	End Time	Net Time	Net Time in Seconds
2	15:30:10.111	15:31:30.666	0:01:20.555	80.555	80.000
3	15:30:15.555	15:32:16.666	0:02:01.111	121.111	121.000
4	1:23:45.678	1:34:56.789	0:11:11.111	671.111	671.000
5	1:23:45.678	2:34:56.789	1:11:11.111	4271.111	4271.000
6	1:23:45.678	1:23:45.789	0:00:00.111	0.111	0.000
7	1:23:45.678	1:23:56.789	0:00:11.111	11.111	11.000
8	1:23:45.678	1:23:46.789	0:00:01.111	1.111	1.000

So where you have 0.111 in row 6, that actually comes out as 0, because even 0.1 of a second is 1.15740740740741E-06 - way too small for excel to manage

[benno64]

the "decimal" seconds are not taken into account here...

Thanks very much for this FDibbins, your formula has worked great! The only thing is I really need an accuracy of 2 decimal places on the final net value on seconds. I'm time keeping a race car event on the Isle of Man, so could really do with this amount of accuracy. (I don't normally use excel as I have my own software and equipment but I'm having to do some time keeping on 1 particular race course where the terrain of the land makes it impossible for me to use my methods so I'm having to input the data into a spreadsheet, where as usually it would be set up to be completely automated)

But... I was pondering over the solution you gave me and thought would it not be possible to use "text to columns function" and have it split the Net Time of the 0:00:00.000 into separate columns using the colons and full stop as a partition, so I could then add the final .000 values onto the Net Time in Seconds values I now have?

However, It seems this won't work with the Delimited function as what gives the figures their colons is the formatting of the cell such as 0:00:00.00. So, for example the delimitated function just reads 0:01:20.555 as 120555 so there are no colons for it to read and use as separators.

I thought I could then use the "Fixed Width" function instead, however a similar problem occurs. The formatting of the cel provides the zeros at the beginning of the Net Values and these zeroes can not be seen by the Text To Column so (as you can see in the screenshot) as everything is bunched to the left instead of the right when I use the fixed width it does not take the last 3 digits of each of the net times (depending on how far to the left they are depends on how much it misses of the last 3 values).

Do you have any idea of how I can just extract the last 3 values out of the net time cells so then I can add them on to the net time in seconds?

Text To Column.jpg
Column Breaks with Fixed Width .jpg

Many Thanks

[alvin-chung]

Yea, it's much easier to see what you're working with and now I see why the earlier solution doesn't work for your spreadsheet

In any new column row 2 (e.g. F2), try this formula and format it as [s].000 will yield the same result as your expectation.

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[benno64]

Try this formula and format it as [s].000 will yield the same result as your expectation.

Please Login or Register  to view this content.

EXCELLENT! This has done exactly what I needed!

Now I need to figure out how to mark this question as solved haha

[benno64]

Try this formula and format it as [s].000 will yield the same result as your expectation.

Please Login or Register  to view this content.

EXCELLENT! This has done exactly what I needed!

Now I need to figure out how to mark this question as solved haha

[alvin-chung]

You're welcome, and thanks for the feedback
ps: try visit http://www.excelforum.com/faq.php for guideline on marking this thread as solved


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin