Summing Military Times

[echomakr1]

I'm working on a sum formula for military times.
The data is imported from an external software package.
I highlight all times in Column D and choose "more number formats" and format all cells in column D to h:mm:ss
I then want to sum Column D to total time worked, I just get Zeroes.

Any ideas? Sheet attached

[63falcondude]

Try this:

=SUMPRODUCT((D2:D125)+0)

Then format as [h]:mm:ss

[echomakr1]

Try this:

=SUMPRODUCT((D2:D125)+0)

Then format as [h]:mm:ss

Solved, you are fantastic, thanks! Can you briefly explain this, if you don't mind. I'd like to understand what you're doing here. Marked thread as SOLVED.

[63falcondude]

Happy to help.

You can select the cell that the formula is in and then go to Formulas > Evaluate Formula

The formula adds 0 to D2 then adds 0 to D3, ... , then adds 0 to D125 and adds up all of the results.

We add 0 to change from text to a number.

Edit: Thanks for the rep!

[AlKey]

Or this

Please Login or Register  to view this content.

[jason.b75]

It appears that your text format times are causing you numerous problems.

If you only need to refer to the time differences in D2:D125 with formulas, then simply entering =C2-B2 into D2, then filling down will give you valid times to work with, solving all of your formula problems.

If you need other formulas to refer to the date / time values in colunn B and C as well, then try this little trick.

Select a single empty cell (any one). Press Ctrl + c to copy it.
Select B2:D125
Press Ctrl Alt v, v, d, enter.

Doing this should convert any numeric values formated as text (this includes dates and times), to proper numeric values.

You may need to re-format columns B and C as mm/dd/yyyy hh:mm:ss after doing this.

Once you have done this, you should be able to use any formulas that you need to without trying to get a valid time from text in each one.

[shg]

Or select the column, do Data > Text to columns, Finish, and then just use a normal SUM formula.

[echomakr1]

Or this

Please Login or Register  to view this content.

Works beautifully, thanks!!

[echomakr1]

Excellent advice, many thanks!

[echomakr1]

It appears that your text format times are causing you numerous problems.

If you only need to refer to the time differences in D2:D125 with formulas, then simply entering =C2-B2 into D2, then filling down will give you valid times to work with, solving all of your formula problems.

If you need other formulas to refer to the date / time values in colunn B and C as well, then try this little trick.

Select a single empty cell (any one). Press Ctrl + c to copy it.
Select B2:D125
Press Ctrl Alt v, v, d, enter.

Doing this should convert any numeric values formated as text (this includes dates and times), to proper numeric values.

You may need to re-format columns B and C as mm/dd/yyyy hh:mm:ss after doing this.

Once you have done this, you should be able to use any formulas that you need to without trying to get a valid time from text in each one.

Excellent advice, many thanks!

[echomakr1]

Or select the column, do Data > Text to columns, Finish, and then just use a normal SUM formula.

Very clean, works great. Thanks!

[jason.b75]

You're welcome!

Shg has suggested an alternative way to achieve the same in post #7, however that one will only work with cells that contain either a date or a time, not both. see edit.

Shg's suggestion has the advantage of being able to work with foreign date formats if needed, whilst mine will only work with dates that match the format of your regional settings.

Either way, both are handy to remember if you will be working regularly with imported data that has numeric values in text formats.

edit:- Text to columns didn't appear to work when I tried it, which is why I suggested the alternative. Looking at it closer, that also appears to be related to regional settings. It fails for me with dates where the day is greater than 12, this implies that changing the dropdown to MDY has no effect when there is a combined date and time and that excel is still trying to convert it in DMY format as per my settings.

If this is the case, then I would imagine that it will work fine for you as the dates are in your local format.

Text to columns, 1 column at a time.

Copy and paste special, any number of rows / columns.

[echomakr1]

You're welcome!

Shg has suggested an alternative way to achieve the same in post #7, however that one will only work with cells that contain either a date or a time, not both.

Shg's suggestion has the advantage of being able to work with foreign date formats if needed, whilst mine will only work with dates that match the format of your regional settings.

Either way, both are handy to remember if you will be working regularly with imported data that has numeric values in text formats.

Ahh, great info, thanks for the follow up!

[jason.b75]

Please refresh the page and take another look at my last reply. I tried it again just to make sure that I hadn't made a mistake when I tried it earlier, and noticed an anomaly, so I've edited my earlier reply to reflect this.

[echomakr1]

Please refresh the page and take another look at my last reply. I tried it again just to make sure that I hadn't made a mistake when I tried it earlier, and noticed an anomaly, so I've edited my earlier reply to reflect this.

Noted, thank you sir.