• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unable to subtract time though date differs

R

rush2rajen

Member
Hi Excel Gurus,

I am unable to subtract time. Though the date differs and also the time differs it should show in one column how many hours had been taken to complete the task rather than showing 1 days and 5 hours for e.g


Thanks
Raaj
 

Attachments

Change Cell E2: =(C2+D2)-(A2+B2)
Format Cell E2 as [hh]:mm
Copy E2 down
 
Both techniques work although they give the same answer in different formats

Your answer is a number
whereas mine is still a time

I did answer your Indirect question in between :)
 
Thanks a lot Hui and thank you Paul.

Hui,
Just one more query How can I show 1 day 6 Hours and 20 mins in cell E2


Thanks
Raaj
 
In E2:
Apply a Custom Number Format like: d "Day" h "Hrs" mm "Min"

If the value is likely to be less than 1 day Then use a Custom Number Format of:
[<1] h "Hrs" mm "Min"; d "Day" h "Hrs" mm "Min"

Which will display both options neatly
 
=CONCATENATE(INT(E2/24)," Days ",TEXT(ROUNDDOWN(E2-(INT(E2/24)*24),0),"##")," Hours ",(E2-(E2-(INT(E2/24)*24)))," Minutes")
 

Attachments

Don't stop Paul
You'll learn more by at least attempting and then seeing others answers
 
You must log in or register to reply here.
Back
Top