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
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 378 members 4,071 guests
Please welcome our newest community members: