Complicated excel formula needed in date format

nitaisorganized

New Member
Joined
Oct 11, 2015
Messages
3
Please help with me with calculating the formula for the due date column:

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 reportRPEDDOADue Date
A08/03/201512/03/201409/17/2015
Q10/09/201510/01/201510/23/2015
En/a10/09/201510/20/2015

<tbody>
</tbody>

Many thanks!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,664
I think your due date for the Q report is off by one day.

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

nitaisorganized

New Member
Joined
Oct 11, 2015
Messages
3
I think your due date for the Q report is off by one day.-

Copy the formula in D2 down.
Sheet10

*ABCD
1Type of reportRPEDDOADue Date
2A8/3/201512/3/20149/17/2015
3Q10/9/201510/1/201510/24/2015
4En/a10/9/201510/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>

Spreadsheet Formulas
CellFormula
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.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,664
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
1Type of reportRPEDDOADue Date
2A8/3/201512/3/2014
3Q10/9/201510/1/201510/23/2015
4En/a10/9/201510/20/2015
Sheet6
 

Forum statistics

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

Some videos you may like

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