Nested If Statements to include a blank cell return

[Hassrona]

Hi I am writing a nested if statement to show due dates for reports based upon their frequency e.g. Daily, weekly monthly. In the if statements I am using the network days add on to add a certain numbers of days to the date sent cell to arrive at the dates due cells. However for the reports which have not been sent, their date sent cells are currently blank. However when I run the nested if formula over the blank cells I get 1/2/1900. I need these cells to be blank. I have tried various if = "" variations and the 1/2/1900 date always returns despite the relevant cell being blank. Any suggestions ? I am using Excel 2013

 

[Panda514]

Hello, network days returns the number of whole workdays between two dates. If you want to create due dates, you should use the workday formula. If the date sent is in cell A2 and you want the assignment due date in cell B2 then type =IF(A2<>0,WORKDAY(A2,5),"") into cell B2. This will give you a due date that is 5 workdays after the assignment date and it will give you a blank cell if there is no assignment date yet.

Here is an example that checks to see what the assignment frequency is instead of typing in the number of days yourself.

Assignment Name	Assignment Frequency	Assignment Sent	Assignment Due
Assign1	Daily	1/1/2019	=IF(A2<>0,WORKDAY(A2,(IF(B2="Daily",1,IF(B2="Weekly",5,IF(B2="Monthly",30,""))))),"")
Assign2	Weekly

<tbody>
</tbody>

I hope this helps!

 

[Hassrona]

Hello, network days returns the number of whole workdays between two dates. If you want to create due dates, you should use the workday formula. If the date sent is in cell A2 and you want the assignment due date in cell B2 then type =IF(A2<>0,WORKDAY(A2,5),"") into cell B2. This will give you a due date that is 5 workdays after the assignment date and it will give you a blank cell if there is no assignment date yet.

Here is an example that checks to see what the assignment frequency is instead of typing in the number of days yourself.

Assignment Name	Assignment Frequency	Assignment Sent	Assignment Due
Assign1	Daily	1/1/2019	=IF(A2<>0,WORKDAY(A2,(IF(B2="Daily",1,IF(B2="Weekly",5,IF(B2="Monthly",30,""))))),"")
Assign2	Weekly

<tbody>
</tbody>

I hope this helps!

Perfect, that works perfectly - thanks very much. One last question, I also have a reminder due date column and a response received column. The reminder due date should only populate when a response date has not been received in the response received column, and only when this is the case two days prior to the due date and also update to one day prior to the due date when the report is due the next day. If the report is due in more than two days time, the cell should remain blank (i.e. Only activating or populating two and then one day in advance of the due date by way of a reminder). This is the last part of the report, any tips would be absolutely fantastic and much appreciated.

 

[Hassrona]

Perfect, that works perfectly - thanks very much. One last question, I also have a reminder due date column and a response received column. The reminder due date should only populate when a response date has not been received in the response received column, and only when this is the case two days prior to the due date and also update to one day prior to the due date when the report is due the next day. If the report is due in more than two days time, the cell should remain blank (i.e. Only activating or populating two and then one day in advance of the due date by way of a reminder). This is the last part of the report, any tips would be absolutely fantastic and much appreciated.

I neglected to mention, I did have a solution, however when the if statement looked up blank cells i.e. for reports not yet worked on thus with no current due date, the if statement returned the value error statement. Essentially the reminder due date column should only be either blank or reference dates two days and then one day prior to the due date. Regards

 

[Panda514]

In my previous post I noticed an error, but it seems like you figured it out. For anyone else reading this, it should be C2 after workday because that is the cell where the date is.

=IF(A2<>0,WORKDAY(C2,(IF(B2="Daily",1,IF(B2="Weekly",5,IF(B2="Monthly",30,""))))),"")

To solve your changing reminder date, I think the best thing to do would be to insert a Today's Date cell at the top of your worksheet using the function =Today() Then you can use if statements that are based off of Today's Date which will update each day. My example below assumes Today's Date is in cell B1. In the reminder date function I set it to blank in the received column instead of "No", but you can change that if you want. It's important to remember that the remind column will be blank if there is anything in the received column. If you want to change it to Yes, then put E3="Yes" instead of E3="" in the formula. Once an assignment is Past Due (assignment due date is less than today's date and there is nothing in the received column), it will say Past Due in the Reminder Date.

Today's Date =Today()

Assignment Name	Assignment Frequency	Assignment Sent	Assignment Due	Received?	Reminder Date
Assign 1	Daily	1/1/2019	=IF(A3<>0,WORKDAY(C3,(IF(B3="Daily",1,IF(B3="Weekly",5,IF(B3="Monthly",30,""))))),"")		=IF(AND(D3=B1-2,E3=""),B1-2,IF(AND(D3=B1-1,E3=""),B1-1,IF(AND(D3<B1,E3=""),"Past Due","")))
Assign 2	Weekly

<tbody>
</tbody>

I hope this is what you needed!

 

[Hassrona]

In my previous post I noticed an error, but it seems like you figured it out. For anyone else reading this, it should be C2 after workday because that is the cell where the date is.

=IF(A2<>0,WORKDAY(C2,(IF(B2="Daily",1,IF(B2="Weekly",5,IF(B2="Monthly",30,""))))),"")

To solve your changing reminder date, I think the best thing to do would be to insert a Today's Date cell at the top of your worksheet using the function =Today() Then you can use if statements that are based off of Today's Date which will update each day. My example below assumes Today's Date is in cell B1. In the reminder date function I set it to blank in the received column instead of "No", but you can change that if you want. It's important to remember that the remind column will be blank if there is anything in the received column. If you want to change it to Yes, then put E3="Yes" instead of E3="" in the formula. Once an assignment is Past Due (assignment due date is less than today's date and there is nothing in the received column), it will say Past Due in the Reminder Date.

Today's Date =Today()

Assignment Name	Assignment Frequency	Assignment Sent	Assignment Due	Received?	Reminder Date
Assign 1	Daily	1/1/2019	=IF(A3<>0,WORKDAY(C3,(IF(B3="Daily",1,IF(B3="Weekly",5,IF(B3="Monthly",30,""))))),"")		=IF(AND(D3=B1-2,E3=""),B1-2,IF(AND(D3=B1-1,E3=""),B1-1,IF(AND(D3<b1,e3=""),"past due","")))<="" td=""></b1,e3=""),"past>
Assign 2	Weekly

<tbody>
</tbody>

I hope this is what you needed


Thanks very much, I am following the instruction, I just want to check the final syntax for the reminder date formula, what should come after (D3 ? I.e. The last D3 referenced ?
Regards

 

[Hassrona]

In my previous post I noticed an error, but it seems like you figured it out. For anyone else reading this, it should be C2 after workday because that is the cell where the date is.

=IF(A2<>0,WORKDAY(C2,(IF(B2="Daily",1,IF(B2="Weekly",5,IF(B2="Monthly",30,""))))),"")

To solve your changing reminder date, I think the best thing to do would be to insert a Today's Date cell at the top of your worksheet using the function =Today() Then you can use if statements that are based off of Today's Date which will update each day. My example below assumes Today's Date is in cell B1. In the reminder date function I set it to blank in the received column instead of "No", but you can change that if you want. It's important to remember that the remind column will be blank if there is anything in the received column. If you want to change it to Yes, then put E3="Yes" instead of E3="" in the formula. Once an assignment is Past Due (assignment due date is less than today's date and there is nothing in the received column), it will say Past Due in the Reminder Date.

Today's Date =Today()

Assignment Name	Assignment Frequency	Assignment Sent	Assignment Due	Received?	Reminder Date
Assign 1	Daily	1/1/2019	=IF(A3<>0,WORKDAY(C3,(IF(B3="Daily",1,IF(B3="Weekly",5,IF(B3="Monthly",30,""))))),"")		=IF(AND(D3=B1-2,E3=""),B1-2,IF(AND(D3=B1-1,E3=""),B1-1,IF(AND(D3<b1,e3=""),"past due","")))<="" td=""></b1,e3=""),"past>
Assign 2	Weekly

<tbody>
</tbody>

I hope this is what you needed


I got the formula to work - using today's date and a series of IF AND statements against today's date only used in conjunction with a blank cell on the response received column. Thanks very much, your assistance has been invaluable !