HOW to split a date and time entered in cell A REQUIRED FORMULA help please I am

Question

HOW to split a date and time entered in cell A REQUIRED FORMULA help please I am new this is my first question

MONTH DATE YEAR HOURS MINUTES SECONDS MILI SECONDS MONTH DATE YEAR HOURS MINUTES SECONDS MILLISECONDS HOURS MINUTES SECONDS MILLISECONDS 09/16/2014 19:00:04:750 09/16/2014 19:00:04:781 09/16/2014 19:00:06:031 09/16/2014 19:00:06:078 09/16/2014 19:00:06:343 09/16/2014 19:00:20:187 As per given data i want seperately enter in each column and total details required like (TOTAL HOURS , 09/16/2014 19:00:20:218 MINUTES AND SECONDS) 09/16/2014 19:00:26:453 09/16/2014 19:00:26:484 09/16/2014 19:00:37:218 09/16/2014 19:00:37:250 09/16/2014 19:00:44:296 09/16/2014 19:00:48:703 09/16/2014 19:00:48:750 09/16/2014 19:01:10:546 09/16/2014 19:01:10:609 09/16/2014 19:01:11:437 09/16/2014 19:01:11:468 09/16/2014 19:01:12:843 09/16/2014 19:01:12:843 09/16/2014 19:01:12:875 09/16/2014 19:01:12:875

Explanation / Answer

Assuming your columns are A:G and the first data is in row 2:

B2 (date):    =INT(SUBSTITUTE(A2,":",".",3))

C2 (time):    =MOD(SUBSTITUTE(A2,":",".",3),1)

D2 (hour): =HOUR(INT(C2*86400)/86400)

E2 (min):   =MINUTE(INT(C2*86400)/86400)

F2 (sec):     =SECOND(INT(C2*86400)/86400)

G2 (msec): =ROUND(MOD(C2*86400,1)*1000,0)

[EDIT] I got hung up on using HOUR etc. The following should work:

D2 (hour): =INT(C2*24)

E2 (min):   =MOD(INT(C2*1440),60)

F2 (sec):      =MOD(INT(C2*86400),60)

G2 (msec): =MOD(ROUND(C2*86400000,0),1000)

Format B2 as Date or Custom mm/dd/yyyy. Format C2 as Custom hh:mm:ss.000.

We cannot simply use HOUR(C2), MINUTE(C2) and SECOND(C2) because Excel rounds fractional seconds. Test with 23:59:59:999.

Caveat: MOD(SUBSTITUTE(A2,":",".",3),1) results in an infinitesimal difference that might be problematic in dependent formulas. It would be better to write --TEXT(MOD(SUBSTITUTE(A2,":",".",3),1),"hh:mm:ss.000"), which converts time to the correct internal representation. The double-negate ("--") or any equivalent arithmetic (multiply by 1 or add 0) converts text to numeric.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.