Convert Seconds into 12 Hour Days

[Mgiffune]

I need to convert seconds into a 12 hour day

For example, starting with 99524 seconds

99524/86400 returns a value of 1.15189815

Formatted in a Custom Format of dd:hh:mm returns 01:03:38 (1 day, 3 hours, and 38 minutes).

However, I need this in days of 12 hours each instead of 24, so the value I'm looking for is 2:03:38 (2 days, 3 hours, 38 minutes).

Any recommendations on how to achieve this would be greatly appreciated.

[Sam Capricci]

I can get you there in two steps though it is off by a minute...
assuming your data is in A2
step 1, use this in cell B2...
=CONVERT(A2,"sec","hr")/12
step 2, use this in cell C2...
=LEFT(B2,SEARCH(".",B2)-1)&" days, "&MID(B2,3,1)&" hours, "&MID(B2,5,2)&" minutes"
it will give you 2 days, 3 hours, 37 minutes
just misses your minutes by one, doesn't round up

I'm sure someone will give you a better one.

[Mgiffune]

Thank you for the reply!

That gets me almost there. At least I now know it's possible.

Is there a way to maintain the format of DD:HH:MM so that the value can be utilized for other calculations?

Or an alternative format that can be used in other calculations?

Thanks!

[CARACALLA]

=int((99524/86400))*2+mod((99524/86400),1)

[Phuocam]

so the value I'm looking for is 2:03:38 (2 days, 3 hours, 38 minutes

Try:

=A1/86400+INT(A1/86400)

[Root_]

I need to convert seconds into a 12 hour day...

Is this what you need?

=INT(A1/43200)+MOD(A1,43200)/86400

Also, a thing to keep in mind: with a dd:hh:mm custom format, you are limited to 31 days.

[Mgiffune]

Thank you for the responses. Several of these seem to be giving the results I'm looking for. I need to play with them to see which will work best.

Much appreciated!

[Mgiffune]

For anyone else looking for this, this gave me the results I was looking for and calculated dd:hh:mm based on a 12 hour clock, computed from seconds (entered in A1)

86400 seconds (24 hours) = 02:00:00
etc


Some of the others worked until the days rolled after 12 hours


=INT(A1/43200)+MOD(A1,43200)/86400