+ Reply to Thread
Results 1 to 13 of 13

Calculation of seconds from Start time and End time

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Calculation of seconds from Start time and End time

    Hi Excel Guru's,

    I would like to calculate the duration in seconds from given start time and end time
    Ex: start time 00:00:02(AM) and End Time (16:47:03)
    Answer is 60,421 in seconds. How do I have to calculate in excel.
    I appreciate your help.

    Regards
    Uday

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    Try:

    =(B1-A1)*24*60*60

    or

    =(B1-A1)*86400

    formatted as Number (or General)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    I have calculate through the formula =(B1-A1)*86400 but some others like example I am getting negative calculation
    Ex: 05/31/2012 23:51:50 06/01/2012 04:20:49 = Duration is -70261 this is not correct
    Correct one is 16,139. I appreciate your prompt reply.
    Thank you once again.

    Regards
    Uday

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    If I place:

    5/31/2012 11:51:50 PM in A1

    and

    6/1/2012 4:20:49 AM in B1

    and use my formula: =(B1-A1)*86400

    I get 16139 as a result.

  5. #5
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    Date and Time are in 4 cells example B1 - 05/06/2012 C1 - 16:20:00 D1 - 05/07/2012 E1 - 02:41:53
    I have a formula =(E1-C1)*86400 = -70261 I am not getting 16139


    Regards
    Uday

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    How about:

    =(D1+E1-(B1+C1))*86400

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    I did it but I am getting different results for other rows 05/31/2012 00:00:02 05/31/2012 16:47:03
    Result 0.69931713 .. Actual results are 60,421 , I would like to have comma after 2 two digits how to format it.
    Regards
    Uday

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    Can you post the sample workbook? I don't know where your calcs are going wrong.

    To format, select the range go to Format|Cells and select Number tab... select Number, and check the Use 1000s separator checkbox... change decimals to 0, if you want no decimals.

  9. #9
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    How to post the workbook?

    Regards
    Uday

  10. #10
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    I have sent the sample workbook to website: [email protected]. Please let me know whether you received the workbook or not.
    I don't know how to post it to the forum

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    Click on the Reply, then click Go Advanced, and click the paperclip icon... browse and upload.. then enter a few words to submit.

  12. #12
    Registered User
    Join Date
    06-13-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    70

    Re: Calculation of seconds from Start time and End time

    Hi Guru,

    I have attached the sample workbook. I appreciate your help.

    Regards
    Uday
    Attached Files Attached Files

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Calculation of seconds from Start time and End time

    Here you go.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1