Using a Macro to add a 0 in cells

[daisiedawn]

We have a spreadsheet with a large amount of data and the time fields are :mm:ss. I need to be able have enter a 0 before the :mm:ss - so my pivot table will read it correctly. ANy thoughts?

[jcaynes]

Are these times just after midnight and before 1:00 am? If so, they are displaying correctly. If not, what are you trying to do with the pivot table?

[daisiedawn]

It's a sum of time. So the examples are the sum of a person's breaks. So I don't need them to be AM or PM - just to include the 0:29:59.

The pivot table is adding each row together and giving an average of them but I'm getting the error because the format isn't complete. Does that help?

[jcaynes]

Is there more data to group by other than just the time?

[daisiedawn]

I'm using the pivot table to sum these columns and average them.

[FDibbins]

These are not times, they are text that is starting to look like a time.

Use this in a helper column, copied down...
=TIMEVALUE(0&A1)

edit: keep in mind that excel treats time as a decimal of 1 (day), so 06:00 AM is actually 0.25, 12 noon is 0.5 etc

[event21]

Hi -

try;

Please Login or Register  to view this content.

event

[daisiedawn]

Thanks the Macro did the job!