•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Nested if formula causing issues
Our take
Nested IF formulas can be a source of frustration, especially when they don’t yield the expected results. In your case, it sounds like you're grappling with conditions that aren't aligning correctly, particularly in row 5. By clarifying the logic for populating columns O:AA based on hire dates and term dates, we can streamline your formula. Let’s explore how to address those "False" values and ensure that each condition returns the anticipated "1A" or "1H" output. Your path to clarity starts here—let’s dive in!
Hi All,
I have been working on this formula for the past 2 days and just cannot bring it to the finish line. I believe I'm missing another false statement somewhere.
Row 5 is where I run into issues with the formula.
The logic:
-Columns O:AA should populate with either "1A", "1H", or blank depending on hire dates, term, dates, or lack of term date.
-I want column O to show 1A if the hire date (column K) is before 1/1/2025 and that there is no term date, or the term date is after 12/31/2025. If this case is not true I want column O to remain blank.
-If column O populates with 1A I want columns P:AA to show blank
-If column O is blank I want to run a formula which looks to see if the hire date (col O) is less than or equal to the start of the subsequent month in CY25. Example: Row 4, I want to assign the value 1A starting in July since their hire date is in June. I want to assign the value 1H in this row to anything before July, since they weren't hired until June.
-If they have both a hire date before or during CY25, and a term date. That is where I'm running into issues. I am getting a "False" value when both of those statements are true. In row 5 it should return "1A" for columns P:S and return "1H" columns T:AA.
My sanity and wife thank you in advance.
I hope I explained what I'm trying to do well enough.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Sum formula for calculating month occurrences from dates works for everything except for January.I am attempting to automate the calculations of how often each month is recorded in another tab. The formula is working for all the other months, but as I am using this template to calculate values as I enter them, I arbitrarily set the limit to 2000, allowing me not to have to think about changing the formula as I enter new information. Unfortunately, it seems that the blank cells are being coded as "1" https://preview.redd.it/7zte9xn701vg1.png?width=972&format=png&auto=webp&s=ba131bec322f84ce55fa9fdbc35cedfcbeb12d9f I have tried going through the formula and found that all the blank cells are coding themselves as "true" only for the month of January (1). I attempted to add =IF(SHEET2!A3:A2000 = " ", 0, ... to the beginning of the formula, but it just gave me a spill error. I might be getting confused between excel vs. Google Sheets formulas. Thank you! submitted by /u/mentallylost14 [link] [comments]
- Trying to add different dates based on different cellsI posted yesterday, but realized there’s a bit more to it than I originally thought. I’ll have an example of what I tried at the bottom. So I need to have dates in one column, (I) reflect different dates based on dates in column, H (created_date), and also based on text in column, B (delayed) and dates in column, G (original_date). Column (I) will have due days of +90 days from column (H) if created on or after 3/1/26. If the date is before that, it needs due dates of column (G)-45 days. Lastly, if column (B) says “extended” or “extended x2” then column (I) needs to have a due date of column (G)-120 days as well. This is the formula I have right now and the issue I’m having is that it’s not populating the correct dates. The formula is input into column (I), and there are blanks sometimes which is addressed in the beginning of the formula. Again, any insight would help! Thank you! =switch([@[created_date]],0,””, [@[created_date]],+IF([@[created_date]]>=Date(2026,3,1),([@[original_dates]]-90),45)+IF([@[delayed]]=“extended”,([@[original_dates]]-120))+IF([@[delayed]]=“extended x2”,([@[original_dates]]-120))) submitted by /u/sadtefany [link] [comments]
- Trying to make a 'Days Since' cell, that checks three other cells for dates (or any text), and outputs a number based on those cells.Hey Excel community! I have three dates on my sheet for each line: the date submitted, the date last actioned and a completion date if completed. https://preview.redd.it/y6q7vfz9l9ng1.png?width=743&format=png&auto=webp&s=78fa957ff21aee508103e3656369b8941ae6cfa1 I am trying to have a 'Days Since' column that outputs the number of workdays since the Last Action date. If there is no Last Action I want it to output the number of workdays since the Submitted date. BUT, if there is a date (or text) in the Completed column, I want it to be empty. I have gotten it to output 'days since' the Last Action date, =IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "") without including the Last Action date itself (the +1), and not being negative for stuff entered today (the MAX(0,). But my attempt to work from the Submit date, if there is no Last Action date haven't been as successful (mostly formula errors) or continuing to accrue days on stuff that has been completed, which I would prefer just be blank. I made some progress that I think is kind of doing what I want, but any line without a date returns a large number that it throwing off my conditional formatting, and I think it is just a messy way of doing what I want: =IF(AND([@[Last Action]]="", [@Completed]=""), MAX(0, NETWORKDAYS([@Submitted] +1, TODAY() )), IF(ISNUMBER([@[Last Action]]), MAX(0, NETWORKDAYS([@[Last Action]] +1, TODAY() )), "")) I was wondering how to make it be blank if there are no dates, and also if there is just a better way to do this..? submitted by /u/dead_pixel_design [link] [comments]
- Nesting multiple AND/IF/OR statementsCurrent formula: =IF(AND(OR(J2=$AE$3,J2=$AE$4),AC2<49,AB2<>"CRMBOOK"),$AE$5,$J$2) Formula looks for two different values in column J, confirms AC is less than 49 and AB does not contain the text "CRMBOOK". If those conditions are true or false it retrieves the respective cells. This works perfect. Now what I'm trying to add here is another condition, where it would look for specific values in column I, and depending on if they are present or not it would change the value of the true reference. I can't figure out the nesting and its driving me nuts... Current: https://preview.redd.it/2gmyy67exstg1.png?width=1464&format=png&auto=webp&s=530ecd1733c4549631c8f964a15e77d554bd8c38 Future: (depending on the value in column I, I want to reference a different value in column AE) https://preview.redd.it/58onvf7qxstg1.png?width=1464&format=png&auto=webp&s=d8fc2a3baabb326d563dae13bf002cda66515f5d Appreciate the help! submitted by /u/Nanergoat22 [link] [comments]