# Complicated excel formula needed in date format

#### nitaisorganized

##### New Member

I need to calculate the due date for 3 different types reports with varying timelines and dependencies. Each due dates must fall on business days but is calculated based on calendar days from pre-defined start dates (if due date falls on a weekend, the formula must auto- select for the previous Friday).
The three types of reports (A, Q, E) will be entered under column "Type of Report"
the two types of start dates (RPED, DOA) will be entered in two different columns "RPED" "DOA".
Report A is due 45 calendar days from its RPED
Report Q is due 15 calendar days from its RPED
Report E is due 11 calendar days from its DOA

eg.
 Type of report RPED DOA Due Date A 08/03/2015 12/03/2014 09/17/2015 Q 10/09/2015 10/01/2015 10/23/2015 E n/a 10/09/2015 10/20/2015

<tbody>
</tbody>

Many thanks!

#### JoeMo

##### MrExcel MVP
I think your due date for the Q report is off by one day.

Copy the formula in D2 down.
Excel Workbook
ABCD
2A8/3/201512/3/2014
3Q10/9/201510/1/201510/24/2015
4En/a10/9/201510/20/2015
 Sheet10

#### nitaisorganized

##### New Member
I think your due date for the Q report is off by one day.-

Copy the formula in D2 down.
Sheet10

 * A B C D 1 Type of report RPED DOA Due Date 2 A 8/3/2015 12/3/2014 9/17/2015 3 Q 10/9/2015 10/1/2015 10/24/2015 4 E n/a 10/9/2015 10/20/2015

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:96px;"><col style="width:68px;"><col style="width:68px;"><col style="width:75px;"></colgroup><tbody>
</tbody>

 Cell Formula D2 =IF(A2="A",B2+45,IF(A2="Q",B2+15,C2+11))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
Dear Joe, Thank you for the response. I believe that the due date is correct on my sheet because the due date fell on a weekend, it was corrected to the friday before. (All saturday and sunday due dates will need to be autocorrected to Friday). Please re-advise.

#### nitaisorganized

##### New Member
If due date falls on Sat or Sun, the formula should correct to Friday. Please let me know.

#### JoeMo

##### MrExcel MVP
If due date falls on Sat or Sun, the formula should correct to Friday. Please let me know.
Sorry, I read your request too quickly and missed the weekend shift to Friday part.
Try this:
Excel Workbook
ABCD
2A8/3/201512/3/2014
3Q10/9/201510/1/201510/23/2015
4En/a10/9/201510/20/2015
 Sheet6

1,081,575
Messages
5,359,711
Members
400,545
Latest member
Damntheman30

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...