dd:hh:mm:ss (Help Please)

D

dewopa

New Member
Joined
Jul 2, 2009
Messages
9
Hello Everyone -

I have a system at work that spits out total time that an employee has been logged in (to a system), in the format of Days:Hours:Minutes:Seconds. This format does me no good so I convert it to either total Hours or Total Minutes.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
For example, 4:13:36:07 would be 4 days, 13 hours, 36 minutes, 7 seconds. Is there a formula or way to have Excel convert 4:13:36:07 into just total hours OR total minutes? <o:p></o:p>
<o:p></o:p>
I have had to do the calculations manually for several months now and I am about to pull my hair out. Any assistance would be GREATLY appreciated!<o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
Pat<o:p></o:p>
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Welcome to the board...

Try MOD

=MOD(A1,1) formatted as time hh:mm:ss

A1 holds the dd:hh:mm:ss
 
Upvote 0
may be this
Excel Workbook
EF
144:13:36:07109
Sheet1
Cell Formulas
RangeFormula
F14=LEFT(E14,SEARCH(":",E14) -1)*24 +HOUR(TIMEVALUE(RIGHT(E14,LEN(E14) -SEARCH(":",E14))))
 
Upvote 0
Thanks.

I tried it. On a blank spreadsheet I entered 4:13:36:07 in A1 and "=MOD(A1,1)" into B1. I get a #VALUE error in B1. A1 is formated as Custom dd:hh:mm:ss and B1 is formated as hh:mm:ss.

Any thoughts?
 
Upvote 0
Thanks Sankar! It seems like it is rounding the hours to a whole number. How would I modifiy it to produce a decimal hours result... ie. 109.34?

Thanks again.


sanrv1f said:
may be this

Excel Workbook
EF
144:13:36:07109
Sheet1
Cell Formulas
RangeFormula
F14=LEFT(E14,SEARCH(":",E14) -1)*24 +HOUR(TIMEVALUE(RIGHT(E14,LEN(E14) -SEARCH(":",E14))))
Click to expand...
 
Upvote 0
OK, so it's a text string, not an actual valid date/time entry...

try
=TEXT(LEFT(A1,FIND(":",A1)-1)+TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1))),"[hh]:mm:ss")
 
Upvote 0
Perfect! You just saved me so much time. Many thanks!!! :biggrin::biggrin:

jonmo1 said:
OK, so it's a text string, not an actual valid date/time entry...

try
=TEXT(LEFT(A1,FIND(":",A1)-1)+TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1))),"[hh]:mm:ss")
Click to expand...
 
Upvote 0
Great, glad to help...

Now that is returning a text string.
If you want it stored as an actual date/time value useable in calculations, you can split out the function into 2 columns.

1 to hold the actual Numerical Date/Time Value
and another to hold the text string

Say
A1 = 4:13:36:07
B1 = LEFT(A1,FIND(":",A1)-1)+TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1)))
C1 = TEXT(B1,"[hh]:mm:ss"0

B1 will be an actual numerical number represenging 4 days 13 hours 36 minutes 7 seconds. It will be useable in other calculations
C1 will be a text string hh:mm:ss (not useable in calculations)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
help is required with Code for the time between readings please
Replies
0
Views
299
microhunt
M
J
  • Question
Conflicting Codes HELP!
Replies
2
Views
157
Jstump
J
M
  • Solved
Help with lookup values - multiple criteria
Replies
3
Views
227
Fluff
Fluff
J
  • Question
Help with conversion of time formula.
Replies
4
Views
587
Legacy 143009
L
W
  • Question
Help needed with creating a macro or function for finding specific keywords & then move to Sheet2
Replies
2
Views
306
kevin9999
K

Forum statistics

Threads
1,214,641
Messages
6,120,692
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top