cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karthi
Level: Powered On

Subtracting working dates and hours to a date

Dear all,

 

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.

 

Please help me to achieve this. 

@Mr-Dang-MSFT @mr-dang 
Screen Shot 12-05-18 at 03.06 AM.PNG

2 REPLIES 2
PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Subtracting working dates and hours to a date

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:

DateAdd(
    DateAdd(    
        DateAdd(
            DatePicker1.SelectedDate,
        Value(TextInput1.Text),Hours),
    Value(TextInput2.Text),Minutes),
-1*Value(TextInput3.Text),Hours)

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.

 

@Dawid @timl @aprildunnam--have you made apps that can calculate new dates and ignore hours out of office?

Super User
Super User

Re: Subtracting working dates and hours to a date

Hi @Mr-Dang-MSFT

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....

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,559)