2013 Power Pivot - Calculate Workdays Between Date - Insert Date When 1 Date Missing

jumpmanz5

New Member
Joined
Dec 3, 2007
Messages
14
Afternoon,

I'm really hoping someone can assist with the formula/calculation to insert a date when one date is missing between the two dates I'm trying to basis the calculation on.

My table [fCompletedPRs] contains information related to procurement requisitions submitted and worked in our system. Part of the data contains dates; Column Names: "Final Approval Date", "Accepted Date" and "Completed Date". I also have a date table that calculates "working day" (0,1).

In general, most records contain a date in each one of these columns, so I'm able to calcuate the # of business days without any issue using the following DAX formula.

=CALCULATE(SUM(dCalendar[WorkingDays]),DATESBETWEEN(dCalendar[Date],fCompletedPRs[Final Approval Date],fCompletedPRs[Completed Date]))

In this formula, I'm calculating the number of work days between "Final Approval Date" and "Completed Date"

My issues is that some records do not contain a "Final Approval Date", but all records do contain an "Accepted Date".

So, what I'm hoping to do, is that when the "Final Approval Date" is blank, insert the "Acceptance Date", so it will then calculate the # of working dates.

Basically, looking to calculate the # of days between two dates and when one of those dates is missing, insert a date, and then do the calculation on those dates.

I would greatly appreciate any help, as I have been plugging away at this all day long.

Thank You
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Afternoon,

I'm really hoping someone can assist with the formula/calculation to insert a date when one date is missing between the two dates I'm trying to basis the calculation on.

My table [fCompletedPRs] contains information related to procurement requisitions submitted and worked in our system. Part of the data contains dates; Column Names: "Final Approval Date", "Accepted Date" and "Completed Date". I also have a date table that calculates "working day" (0,1).

In general, most records contain a date in each one of these columns, so I'm able to calcuate the # of business days without any issue using the following DAX formula.

=CALCULATE(SUM(dCalendar[WorkingDays]),DATESBETWEEN(dCalendar[Date],fCompletedPRs[Final Approval Date],fCompletedPRs[Completed Date]))

In this formula, I'm calculating the number of work days between "Final Approval Date" and "Completed Date"

My issues is that some records do not contain a "Final Approval Date", but all records do contain an "Accepted Date".

So, what I'm hoping to do, is that when the "Final Approval Date" is blank, insert the "Acceptance Date", so it will then calculate the # of working dates.

Basically, looking to calculate the # of days between two dates and when one of those dates is missing, insert a date, and then do the calculation on those dates.

I would greatly appreciate any help, as I have been plugging away at this all day long.

Thank You


No one has any thoughts??
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top