Nesting IF formulas - Date Heavy

Masugahau

New Member
Joined
Jul 20, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I am trying to create a budgeting workbook that would take data entered into one sheet and use it to complete sections of subsequent sheets. In this example, the sheet where data is entered is titled Instructions. To begin, users need to complete three fields on this sheet:
  1. Field 1 is a dropdown where they will select how frequently they are paid (Weekly, Bi-Weekly, Monthly, or Twice a Month)
  2. Field 2 is field to enter their next paydate
  3. Field 3 is a field to enter the subsequent paydate after the next paydate
The sheet where data is being populated is called "Full Budget." On this sheet, I am attempting to populate pay dates based on the data entered on the Instructions sheet. I am trying to use a nested IF formula to calculate the dates. I was able to do so for instances where the selected pay frequency is Weekly, Bi-Weekly, and Monthly. Here is the formula I wrote and tested:
=IF(Instructions!C5="weekly",(A3+7),IF(Instructions!C5="bi-weekly",(A3+14),IF(Instructions!C5="monthly",(EDATE(A3,1)))))

This works correctly in my testing. I am running into trouble when I try to add the final IF formula - =IF(Instructions!C5="Twice a Month",Instructions!F4)
When I put this formula on it's own in a different cell, it calculates correctly. When I try to add it to my nested IF formula, it fails. The error I get is a standard "There's a problem with this formula." If I try to insert this section at the beginning of the nested IF formula, I get the #VALUE error.

I cannot figure out what I'm doing wrong here. Any suggestions?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sounds like you just tried to put it the wrong side of a bracket or missed a comma.

=IF(Instructions!C5="weekly",A3+7,IF(Instructions!C5="bi-weekly",A3+14,IF(Instructions!C5="monthly",EDATE(A3,1),IF(Instructions!C5="Twice a Month",Instructions!F4))))

You will get #VALUE! if A3 contains anything other than a valid date.
 
Upvote 0
Sounds like you just tried to put it the wrong side of a bracket or missed a comma.

=IF(Instructions!C5="weekly",A3+7,IF(Instructions!C5="bi-weekly",A3+14,IF(Instructions!C5="monthly",EDATE(A3,1),IF(Instructions!C5="Twice a Month",Instructions!F4))))

You will get #VALUE! if A3 contains anything other than a valid date.
THANK YOU! I still can't see it when I look myself, but your formula worked and saved my day!! :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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