Time Format

[-emma-]

Hi all,

I've had some brilliant help from here recently - thank you

But have now encountered another problem.

At work we are sent reports from automated from systems (not owned by us)

We can manipulate these reports pretty well but there is one aspect I cant solve myself.

One column of data shows times staff have taken for breaks. This displays in the format below:

Column A Column B
Staff Name 1 0:00:15:00
Staff Name 2 0:00:19:00
Staff Name 3 0:00:16:00
Staff Name 4 0:00:01:00

I need to format Column B in order to be able to add it up as in this format it just counts the cells. The format i need (I think) is [h]:mm:ss. Could anyone throw any suggestions my way please?

Thanks for reading

Emma

 

[DRSteele]

I can't see why there are three colons. Perhaps you can use a formula or other method to hack off the :00 on the right, or you can replace the colon with a decimal (as in "portions of a second") if those numerals have a meaning in your milieu.

 

[Rick Rothstein]

Hi all,

I've had some brilliant help from here recently - thank you

But have now encountered another problem.

At work we are sent reports from automated from systems (not owned by us)

We can manipulate these reports pretty well but there is one aspect I cant solve myself.

One column of data shows times staff have taken for breaks. This displays in the format below:

Column A Column B
Staff Name 1 0:00:15:00
Staff Name 2 0:00:19:00
Staff Name 3 0:00:16:00
Staff Name 4 0:00:01:00

I need to format Column B in order to be able to add it up as in this format it just counts the cells. The format i need (I think) is [h]:mm:ss. Could anyone throw any suggestions my way please?

That first 0: is not part of a standard time value... if you first remove it and then force the cells to real Time values, you will then be able to do the kinds of calculations you want to do.

 

[-emma-]

That first 0: is not part of a standard time value... if you first remove it and then force the cells to real Time values, you will then be able to do the kinds of calculations you want to do.

Hi,
Ive tried doing this (using =RIGHT(B1,8) in column C) bringing 00:15:00 through but still cant sum these figures.

Any suggestions on how you would do this please?

Thanks

 

[Rick Rothstein]

Hi,
Ive tried doing this (using =RIGHT(B1,8) in column C) bringing 00:15:00 through but still cant sum these figures.

Try using this formula...

=0+RIGHT(B1,8)

which will return the time a a serial number which you can Cell Format to display as a recognizable time value... and now you will be able to calculate with them.

 

[-emma-]

Try using this formula...

=0+RIGHT(B1,8)

which will return the time a a serial number which you can Cell Format to display as a recognizable time value... and now you will be able to calculate with them.

Works perfectly - Thank you

 

[jtakw]

Hi,

That first 0, I believe, represents Days, however, I don't think Excel recognizes this format, if you don't have Any data that has a value other than 0 at the beginning, you won't need it and can remove it.
If you Do have values other than 0, than you'll need to multiply that number by 24 and just add the result to the hours.