+ Reply to Thread
Results 1 to 8 of 8

Formula for calculating time difference between two cells

  1. 03-05-2015, 03:04 AM #1
    mprasanna
    mprasanna is offline
    Registered User
    Join Date
    03-04-2015
    Location
    Pune,India
    MS-Off Ver
    2003
    Posts
    3

    Post Formula for calculating time difference between two cells

    Hi Everyone,
    I am new to this forum so please forgive me if the way I am posting my problem is wrong.
    In the attached excel file in column A there is a date & time format when I received order.
    In column B it is the end date of the order which I created as 48 hrs in column F.
    In column C it is the current time linked with system time.
    In Column D I want the time remaining for the order that is the difference between B3 & C3 as HH:mm.
    I have tried it but it is not getting correct answer.
    I am attaching excel file here.

    Any help will be most appreciated.

    Kind Regards
    Prasanna
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-05-2015, 03:38 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,999

    Re: Formula for calculating time difference between two cells

    the answer is coming with HH:MM format then what you want ??
    Reply With QuoteRegister To Reply
  3. 03-05-2015, 04:05 AM #3
    mprasanna
    mprasanna is offline
    Registered User
    Join Date
    03-04-2015
    Location
    Pune,India
    MS-Off Ver
    2003
    Posts
    3

    Re: Formula for calculating time difference between two cells

    Yes you are right.There is no issue of FORMAT. It is actually calculating that time wrongly.
    I have added 48 hrs (Column F) in the A3 cell to get the B3. & from B3-C3 it is showing me just 23hrs which should be @ 46 hrs.
    Reply With QuoteRegister To Reply
  4. 03-05-2015, 04:24 AM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,750

    Re: Formula for calculating time difference between two cells

    Have you tried formatting [hh]:mm as you have in F1 ?

    It behaves differently and will present hours / minutes as you indicate (D3).

    With the formulas you have I don't see how you could get 23 or 46...........
    Edit: Never mind..........different time zones.

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    Start Date
    End Date
    Current Time
    Time Remaining
    		 Duration 48:00
    2
    3
    3/5/15 12:01 PM
    3/7/15 12:01 PM
    3/5/15 2:30 AM
    57:30
    Last edited by FlameRetired; 03-05-2015 at 04:39 AM.
    Reply With QuoteRegister To Reply
  5. 03-05-2015, 04:40 AM #5
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2503 Build 16.0.18604.20000) 64-bit
    Posts
    3,999

    Re: Formula for calculating time difference between two cells

    Now please check the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 03-05-2015, 04:46 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Formula for calculating time difference between two cells

    =TEXT(IF(A3="","",B3-C3),"[H]:MM") Try this or
    =IF(A3="","",B3-C3) AND CHANGE THE CELL FORMAT AS [h]:mm
    Format Cells > Number > Custom
    in Custom under type select [h]:mm
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  7. 03-05-2015, 04:52 AM #7
    mprasanna
    mprasanna is offline
    Registered User
    Join Date
    03-04-2015
    Location
    Pune,India
    MS-Off Ver
    2003
    Posts
    3

    Re: Formula for calculating time difference between two cells

    Thanks It's working as per my requirement.
    Reply With QuoteRegister To Reply
  8. 03-05-2015, 04:54 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Formula for calculating time difference between two cells

    You are welcome and thanks for your feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating time difference between differently formatted cells querry
    By Nige78 in forum Excel General
    Replies: 4
    Last Post: 10-31-2014, 09:32 AM
  2. [SOLVED] Calculating time difference between two days combined with IF formula
    By kj2607 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-30-2014, 06:20 AM
  3. [SOLVED] Calculating the Difference in Time
    By forexcellence13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2013, 10:56 PM
  4. Calculating time difference
    By TomBP in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-02-2010, 04:59 AM
  5. Calculating time difference between two days capturing the overnight time.
    By Monica_La in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2007, 11:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1