Formula between 2 dates and times

[IKHAN]

I Need to calculate total hours and minutes between 2 dates in column Cand D and output in E

Format of dates in Column C and D : ddd mmm dd, yyyy - hh:mm AM/PM
Format of Column E : hh:mm

D14 : Sat Dec 02, 2016 - 12:30 PM
C14 : Thu Dec 01, 2016 - 09:00 AM

Output in Cloumn E14 : hh:mm

Help will be appreciated..

 

[Chihiro]

Is it text or date value in C14 & D14?

If in text format...

=SUBSTITUTE(RIGHT(D14,LEN(D14)-4),"- ", "")-SUBSTITUTE(RIGHT(C14,LEN(C14)-4),"- ","")

E14 format: [h]:mm

Edit: If date value...
=D14-C14
Same format for E14 as above

 

[IKHAN]

Thanks Chihiro

This worked too

= MOD(D14-C14,1)

 

[Chihiro]

= MOD(D14-C14,1)

This would give wrong result if difference is greater than 24 hours between 2 values.

As 1 represents 1 day (i.e. 24 hours), MOD function will only return 3:30 instead of 27:30 (using your example datetime).

 

[IKHAN]

Aha ..With above both formulas , It doesn't calculate after 24 hours

D14 : Sat Dec 02, 2016 - 12:30 PM
C14 : Thu Dec 01, 2016 - 09:00 AM

Gives out 3.30 in E

Output reqd. : 27:30

 

[IKHAN]

Thanks Chihiro ... Your formula works perfectly..had Missed E14 format: [h]:mm

 

[nkunni]

=SUBSTITUTE(RIGHT(D14,LEN(D14)-4),"- ", "")-SUBSTITUTE(RIGHT(C14,LEN(C14)-4),"- ","")


Hi, Chihiro

If remove -4 from this formula we can calculate
more than one year. Result as hh:mm.

 

[Chihiro]

Hmm? My formula should calculate difference between any text dates in OP's format (within Excel constraints). -4 is just used to remove weekday portion from string.