locked
How can I calculate time difference between start time and end time even if date is different? RRS feed

  • Question

  • Hi,

    I have query that how can I calculate difference between two time even if date is different?

    For example if start time on date 23/08/2012 is 20:30:00 and end time on date 24/08/2012 is 06:32:00 then how can I calculate difference between these two in MS-ACCESS database?

    Thank you.

    Friday, August 24, 2012 10:09 AM

Answers

  • Hi

    You can calculate the difference between the two dates with times in to hours and then recalculate to hours to get days...

    ie., convert the first time to mins, second also into mins., get the difference and convert the mins into days and hours.

    This should solve your problem.

    Regards
    Repath.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 10:27 AM
  • Hi,

    do you need the number of days within this difference or smth special? One way is rather simple: just a subtraction:

    #24/08/2012 06:32:00#-#23/08/2012 20:30:00#
     0.418055555557658

    And then using CDate() function you'll get an appropriate date format:

    CDate(#24/08/2012 06:32:00#-#23/08/2012 20:30:00#)
    10:02:00

    If you want to get the number of days as well, the formula becomes a bit bigger, e.g.:

    int(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#) & " day(s) " & cdate(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#-int(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#))
    1 day(s) 10:02:00

    But if you need something special, I would use VBA and create a smarter function with all available date parts etc.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    • Edited by Andrey Artemyev Friday, August 24, 2012 10:35 AM
    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 10:33 AM
  • I believe this should be close:

    Format((DateDiff("s",[dtmStart],[dtmFinish]-DateDiff("d",[dtmstart],[dtmfinish]))/60/60/24),"h:nn:ss")

    You can use 'Format' if a string representation is okay or you can conver it by encapusating in with 'CDate' like Andrey Artemyev stated.




    • Edited by DeDBlanK Friday, August 24, 2012 12:13 PM not awake yet
    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 11:52 AM

All replies

  • Do you have start date and start time in a single date/time field, or in two separate fields? (And the same for the end date and end time)

    In what unit do you want to calculate the time difference? Minutes, hours, days?


    Regards, Hans Vogelaar

    Friday, August 24, 2012 10:22 AM
  • Hi

    You can calculate the difference between the two dates with times in to hours and then recalculate to hours to get days...

    ie., convert the first time to mins, second also into mins., get the difference and convert the mins into days and hours.

    This should solve your problem.

    Regards
    Repath.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.

    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 10:27 AM
  • Hi,

    do you need the number of days within this difference or smth special? One way is rather simple: just a subtraction:

    #24/08/2012 06:32:00#-#23/08/2012 20:30:00#
     0.418055555557658

    And then using CDate() function you'll get an appropriate date format:

    CDate(#24/08/2012 06:32:00#-#23/08/2012 20:30:00#)
    10:02:00

    If you want to get the number of days as well, the formula becomes a bit bigger, e.g.:

    int(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#) & " day(s) " & cdate(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#-int(#25/08/2012 06:32:00#-#23/08/2012 20:30:00#))
    1 day(s) 10:02:00

    But if you need something special, I would use VBA and create a smarter function with all available date parts etc.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    • Edited by Andrey Artemyev Friday, August 24, 2012 10:35 AM
    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 10:33 AM
  • I believe this should be close:

    Format((DateDiff("s",[dtmStart],[dtmFinish]-DateDiff("d",[dtmstart],[dtmfinish]))/60/60/24),"h:nn:ss")

    You can use 'Format' if a string representation is okay or you can conver it by encapusating in with 'CDate' like Andrey Artemyev stated.




    • Edited by DeDBlanK Friday, August 24, 2012 12:13 PM not awake yet
    • Marked as answer by Dummy yoyo Tuesday, September 4, 2012 3:21 AM
    Friday, August 24, 2012 11:52 AM