Forum

Subtracting dates i...
 
Notifications
Clear all

Subtracting dates in Power Query and negative numbers

3 Posts
2 Users
0 Reactions
61 Views
(@kendrickg)
Posts: 11
Active Member
Topic starter
 

I'm in the middle of a query and need to subtract dates for days between appointments.  Some days will equal a negative number based on the dates.  I only wish to keep the positive numbers and the negative numbers I don't want to count.  I would really like to create a formula where if the number is a negative number it returns a zero or in power query I can change it to a zero in the cell.  Any ideas.  I created an additional column and subtracted one date column from another and change the type to a duration type.  Any assistance would be appreciated.

Thank You,

Gary 

 
Posted : 09/01/2019 11:07 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Gary,

You can 'Add Column' > Custom Column:

= if [First Date] > [Second Date] then 0 else Duration.Days([First Date] - [Second Date])

Then set the data type as Duration.

Mynda

 
Posted : 10/01/2019 2:48 am
(@kendrickg)
Posts: 11
Active Member
Topic starter
 

Thanks Mynda,

I'll try this today.  This will be a big help for my ultimate goal.

 
Posted : 10/01/2019 9:47 am
Share: