Specific Date and Time calculation

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dtolemy
    New Member
    • Apr 2017
    • 17

    #16
    Thanks again Phil for be so patient with me and helping me through this.

    Comment

    • dtolemy
      New Member
      • Apr 2017
      • 17

      #17
      Phil, Are you out there? I am having an issue with the code above and not sure why. If I add an additional hour to the example above and make the end date 5/24/17 15:50 PM instead of adding an hour to 38.05 it subtracts and hour and makes it 36.05. What do I need to change?

      Comment

      • PhilOfWalton
        Recognized Expert Top Contributor
        • Mar 2016
        • 1430

        #18
        Sorry, the result in the original picture was wrong. I think the answer should gave been 35:25, not 37:05

        So you need to change 4 lines of code - it's obvious which ones
        Code:
            ' First Day (Subtract minutes before the hour)
            SumMins = SumMins + (Hour(TimeValue(DLookup("DayEnd", "TblDays", "DayID = " & Weekday(StartDate)))) - Hour(TimeValue(StartDate))) * 60
            SumMins = SumMins + Minute(TimeValue(DLookup("DayEnd", "TblDays", "DayID = " & Weekday(StartDate)))) - Minute(TimeValue(StartDate))
           
            ' Last day (Add minutes after the hour)
            SumMins = SumMins + (Hour(TimeValue(EndDate)) - 1 - Hour(TimeValue(DLookup("DayStart", "TblDays", "DayID = " & Weekday(EndDate))))) * 60
            SumMins = SumMins + 60 - (Minute(TimeValue(DLookup("DayStart", "TblDays", "DayID = " & Weekday(EndDate)))) + Minute(TimeValue(EndDate)))
        See if that works

        Phil

        Comment

        • dtolemy
          New Member
          • Apr 2017
          • 17

          #19
          Phil, I tried the code but I am still getting the same result it's almost like it should be reverse Like if I change the end time from the original 2:50 to 1:50 it should calculate down from 37.05 to 36. 05 and yet it calculates it up going to 38.05. When I change the time form 2:50 to 3:50 it should calculate to 38.05 and instead it calculates to 36:05 so instead of adding it is subtracting. I am including a file that has the latest code that I have.
          Attached Files

          Comment

          • PhilOfWalton
            Recognized Expert Top Contributor
            • Mar 2016
            • 1430

            #20
            Very odd
            Assuming the table is as my very early posting and with
            Start Date 22/05/2017 09:45:00
            End Date 24/05/2017 14:50:00
            I get 35:25

            With
            Start Date 22/05/2017 09:45:00
            End Date 24/05/2017 15:50:00
            I get 36:25

            Are we agreed that those are the correct times, not the 36:05 and 37:05?

            Phil

            Comment

            • dtolemy
              New Member
              • Apr 2017
              • 17

              #21
              Phil I manual figured out the difference for the 22/05/2017 09:45:00 and the end date of 24/05/2017 14:50. Based on the table where Monday thru Friday is 6:00 am to 22:00 pm it does come out to 37.05. If you add 1 hour to the end date and make it 15:50 pm when you calc it should show 38.05 because you are adding an hour instead, it shows 36.05 which is actually an hour less. I went back and re loaded your original program which is the same as I had and I still get the same result. Is there a way to trick it to think that when the end time is increased it actually adds an hour to the end time versus taking it away? This would have to apply to the Start Date as well. Your thoughts

              Comment

              • PhilOfWalton
                Recognized Expert Top Contributor
                • Mar 2016
                • 1430

                #22
                Must be going senile

                The calculation for the First day and the whole days is correct. It is as you suspected the last day calculation that was wrong. I was just being stupid

                Try
                Code:
                 ' Last day (Add minutes after the hour)
                    SumMins = SumMins + (Hour(TimeValue(EndDate)) - Hour(TimeValue(DLookup("DayStart", "TblDays", "DayID = " & Weekday(EndDate))))) * 60
                    SumMins = SumMins + Minute(TimeValue(EndDate)) - (Minute(TimeValue(DLookup("DayStart", "TblDays", "DayID = " & Weekday(EndDate)))))
                Basically all the right words, but not neccessarily in the right order!!

                Phil

                Comment

                • dtolemy
                  New Member
                  • Apr 2017
                  • 17

                  #23
                  Phil, this is now working as expected, thank you again so much for your help. Dave

                  Comment

                  Working...