Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    15

    Red face Unanswered: Display Hours and Minutes in Days

    Hello All
    I would be grateful for any help.

    I have a simple form for car hire. Two date/time fields for carhirestart and carhirefinish in which I enter both date and time in each:

    carhirestart: 23/06/2006 10:15:00
    carhirefinish: 23/06/2006 16:00:00

    I then have another field which calculates the time of car hire in minutes which I have the code as:
    =Int(DateDiff("n",[CarHireStartDate],[CarHireFinishDate]))

    I have another field which converts the minutes to hours and minutes which I have the code as:
    =Format(Int((DateDiff("n",[CarHireStartDate],[CarHireFinishDate]))/60),0) & " Hours and " & Format((DateDiff("n",[CarHireStartDate],[CarHireFinishDate]))-(Int((DateDiff("n",[CarHireStartDate],[CarHireFinishDate]))/60)*60),0) & " Minutes"

    On the form it looks like:
    5 Hours And 45 Minutes

    Everything works fine up to now.

    I now want a field that converts the hours and minutes into days. The hire period is 24 hours so if a car is returned after 24hours and 5 mins this is classed as a 2 day car hire and charged for 2 days. Therefore I need something like:

    if the carhirestart is between 0 (minutes) and 1440 (minutes) then show 1 Day, if the carhirestart is between 1441 (minutes) and 2880 (minutes) then show 2 days, if the carhirestart is between 2881 (minutes) and 4320 (minutes) then show 3 days etc.

    Any help would be brilliant.
    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    What you want is similar to calculating an age using a birthdate, which is in the help file HERE.

    Using what you have already, let's name the control holding the number of minutes as txtMinutes.

    =Round([txtMinutes]+0.49999,0)

    Since your smallest increment of time is 1 second, which is 0.016666667 minutes, there are enough 9's on the .49 to ensure 24 hours and 1 second rounds to 2 days.

    tc

  3. #3
    Join Date
    Feb 2006
    Posts
    15
    Hi, thanks for replying. I have tried what you said but all I still get is the total minutes, it's not split up into days.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    =Round([txtMinutes]/1440+0.49999,0)

    My bad, I forgot to divide.

  5. #5
    Join Date
    Feb 2006
    Posts
    15
    Hi, that's absolutely brilliant. Thank you so very much. Works like a dream.

Posting Permissions

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