• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Help me with Time Conversion in Access

I have StartDate and EndDate. How to find the difference in business hours and minutes
StartDate                      EndDate                       
5/20/2013 9:32      5/21/2013 16:24      
5/20/2013 9:32      5/21/2013 16:27      
5/20/2013 9:32      5/21/2013 16:29      
5/20/2013 9:32      5/21/2013 16:31      
I am using formula to convert to Minutes:
Minutes: DateDiff("n",[StartDate],[EndDate])
TurnAround Time: Int(([Minutes]\60)\24) & ":" & Format(([Minutes]\60) Mod 24,"00") & ":" & Format([Minutes] Mod 60,"00")
it does not work
and I need to  count only business (8 hours)
  • 2
1 Solution
Dale FyeCommented:
I would recommend you create a function for this.  Pass the function the two date/time values, then use some logic to determine whether:

1.  The two values are on the same date (then use datediff)
2.  If the two values are on two successive dates, in which case you have to do the date diff between date/time1 and your end of day (whatever time that is), and then another calculation for the difference between your start time and date/time2.
3.  If there is more than one day between them, you would need to do the same as #2, plus for the extra days, determine whether they are business days or not and add the appropriate number of minutes.
Shaun KlineLead Software EngineerCommented:
Try this to get the business minutes:
DateDiff("n", startdate, enddate) - DateDiff("d", startdate, enddate) * 16 * 60

You would need to check that enddate is greater than startdate.
rfedorovAuthor Commented:
Thank you, but i did not get
rfedorovAuthor Commented:
To: Shaun_Kline thank you works fine
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now