Starfleet captains are like children. They want everything right now and they want it their way. But the secret is to give them only what they need, not what they want.-Montgomery Scott

 Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum Access Troubleshooter Access Developer Network Access AI Bot ------------ Excel Courses Excel Index ------------ Code Vault Testimonials Tip Jar Advice for Consultants

 Back to Excel: Separating Time & Date    Comments List
 John Mosher  16 years ago
I've been struggling with this myself because I have a database that was exporting an elapsed time as a date and time. I figured out the following single cell solution.

Assuming the date and time is in cell A1 use the following formula =MOD(A1,1). You can then format the results with whatever time format you want.

This works because the MOD function computes the remainder after dividing by the number to the right of the comma. In this case dividing by 1 leaves only the numbers to the right of the decimal.

In Lauren's example this leaves a decimal of 0.2565. When formatted with the appropriate time function you get the format Lauren is looking for.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Excel: Separating Time & Date.