Formatting a cell for Time

[bigd1320]

Hello,

I have a spreadsheet with a column for Duration. I formatted the column Custom [h]:mm:ss. The formatting will only take if I highlight the cell and press F2 then enter.

I have 16000 entries to do this with for each month. Is there a way run a macro to do this for me or some how except the format for each cell?

I am attempting to run SUMPRODUCT on the duration of time, but the cells will not be included in the function unless I run F2 and enter on each cell.

Thanks,
Dan

[AliGW]

Welcome to the forum.

That sounds wrong. Can you share a workbook demonstrating the problem?

There are instructions at the top of the page explaining how to attach your sample workbook.

[MrShorty]

I have a spreadsheet with a column for Duration. I formatted the column Custom [h]:mm:ss. The formatting will only take if I highlight the cell and press F2 then enter.

That sounds like the time values are "numbers stored as text". F2-enter is only one strategy for converting numbers stored as text to real numbers. Other strategies here: https://support.microsoft.com/en-us/...rs=en-us&ad=us

[bigd1320]

If you look at tab Jan 2020 column K is Duration. I formatted the column but the time will not add to the bottom sum unless I click on the cell, press F2 then enter. K3 thru K6 have not been processed with F2. You should see the total increase as the each cell is processed.

I took a great number of rows out to show this. I am just trying to figure out how to except the time format without entering each cell separately.

Thanks,
Dan

[ChemistB]

Try this
Select the column (one column at a time), go to Data Ribbon Tab and select Text to Columns
Just click "Finish" when the Text To Columns window opens.
Did that fix the problem?

[AliGW]

It's because the entries are text, not real times. The F2 process converts them, as does entering the cursor in the cell and hitting ENTER.

[bigd1320]

Text to Columns is the tool I needed. Thanks for the Help!!