Thanks again Phil for be so patient with me and helping me through this.
Specific Date and Time calculation
Collapse
X
-
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
-
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)))
PhilComment
-
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 FilesComment
-
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?
PhilComment
-
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 thoughtsComment
-
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)))))
PhilComment
Comment