Based on this Post I could successfully able to add hours to working dates. Now i wanted to do the reverse thing. Wanted to subtract hours and go backwards to previous dates based on duration.
For eg. If the selected date is today (5th December) and start time is 8 AM and if i need to minus 1 hours backwards it should minus and go to the previous workdays and expected results should be previous date (4th December) and last one hour (5pm -1hr) so 4th december 4PM.
DateDiff is used for calculating elapsed time between two dates.
DateAdd() is the function you want to calculate a new date. It sounds odd because it's adding. To subtract, you would just add a negative number. The best part about DateAdd() is you can choose what unit to add/subtract.
So in your case, you'll subtract 1 hour by using a formula like this:
Starting from the inside I'm adding the selected date to the hours and minutes specified. This gives me a datetime to start from.
Then I'm subtracting whatever is typed into the bottom box (multiply by -1 to make it negative).
The solution above subtracts the time as is. But you want to ignore the hours outside of work. That's a fun problem to solve. I'm thinking from our community, we probably have people who have solved timesheets and can share a solution.
I've not created any apps to ignore hours out of office. But if it were me, I would probably write something in C# and call it via a custom connector. I do think this is an excellent question and I have been thinking of how to solve it. If I come up with anything, I'll post back....