#VALUE when converting Time to whole number and decimal

Hi guys,

It was a while since I have visited this forum. I have learned a lot from here specially to the kind and helpful people and moderators. Anyway, I am presented with time data which are mix with seconds and time. Like column A1-A5 would contain 59 seconds, 15 seconds, 25 seconds, 1:05 (1 minute and 5 seconds) and 1:12:45 (1 hour, 12 minutes and 45 seconds)....Now the challenge here is that when I add all these time data and convert all of them in seconds like =SUM(A1:A5)*86400...I get a #VALUE error message? How can I get the sum and convert the total result into whole number in seconds, minutes and hours and also get them in decimal value? I hope I don't sound confusing?? Any idea guys how to accomplish this?

Originally Posted by martindwilson

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

Hi Martin,

Thanks for replying...Here is the attached sample workbook. Thanks in advance.

Originally Posted by martindwilson

unfortunately 1:05 is 1 hour 5 minutes it needs to be 00:01:05
otherwise you could use array entered
=SUM(IF(A2:A6<1,A2:A6*86400,A2:A6))

Hi again,

Tried the formula provided however I still received the #value error message. I am guessing that it has something to do with the mixed data presented which are in whole number and time format. Please see Sheet 2 of attached file. Thanks.

Im sorry but the server went down when posting my reply...just want to make sure that my response appears on the recent post...reposting only and still needs a reply ...thanks

Originally Posted by romperstomper

What is 1:4 meant to be? 1:40 or 1:04?

Hi there,

1:4 means 1 minute and 4 seconds...I know this is an incorrect value in excel but this was how the data is presented on the report that I am working with. That's why I am having this challenge...The data are extracted from a website thus they have different time format appearance...

Originally Posted by romperstomper

Try selecting your data, then press Ctrl+H, click in the Find box and hold the Alt key while typing 0160 on the number keypad, leave the Replace box blank and press Replace All. That should convert your data to numbers/times. You can then use Martin's array formula:
=SUM(IF(B5:B22<1,B5:B22*86400,B5:B22))
which must be entered using Ctrl+Shift+Enter rather than just Enter.

Hi Romp,

Ok..tried your suggestion and yeah it worked but is there anyway that I don't have to do those procedures...I mean that ALT 0160 something? Is it possible to automate such that when I paste it on excel it automatically converts the data into time? also how do I convert those seconds into minutes (whole number and decimal format)

Originally Posted by stoey

Hi Romp,

Ok..tried your suggestion and yeah it worked but is there anyway that I don't have to do those procedures...I mean that ALT 0160 something? Is it possible to automate such that when I paste it on excel it automatically converts the data into time? also how do I convert those seconds into minutes (whole number and decimal format)

and by the way the formula given by martin? which is =SUM(IF(A2:A6<1,A2:A6*86400,A2:A6))....i changed the 86400 to 1440 to get the total seconds...still up for the previous issue...how can I automate in such that the data that I will paste which were extracted from the web be converted to time format without having to do the alt0160 thingy?