I have a small test spreadsheet similar to one I use at work where I'm testing out some things and trying in a number of columns to populate cells based on conditions of other cells to cut down on the amount of inputting needed. I have the basic formulas figured out, and it's all working correctly, except that for one condition it's not quite giving the result I need.
The spreadsheet holds data on surveys conducted on where repairs are needed to roads and footpaths. As part of the spreadsheet I have two columns - one that shows the date repair work was completed, and another that displays either "completed" or "repair outstanding" based on whether there's a date in the column that shows the date the repair was completed or not, which I've also got colour-coded using conditional formatting. This is the formula I'm using:
This works fine, except where a survey's been completed but no repair work is required, where because there's no date in the column with the date completed, though no work is needed, it still shows "repair outstanding". There's a further column for the current status of the repair which shows the status of the repair request based on the severity of the repair needed - whether it's urgent and needs completing immediately so is shown as "pending" or not so severe so has been put on a work programme to be completed and is shown as "programmed". This column also shows "completed" for completed repairs, and "repair not required" for those not requiring repairs.
What I'm trying to achieve is changing the formula to check whether this third column shows "repair not required" and leave the cell blank if either no repair's required or there's no data in that cell. In other parts of the spreadsheet I'm successfully using nested IF statements to check the condition of two cells, but for some reason I can't get the same approach to work in this formula using ISNUMBER.
I have a similar problem with another column where I want the financial year to display (e.g. 2017/18), based on the date of the survey. This too is working fine, except where no date is entered, where it shows "1899/00", where I want the cell to remain blank if no date is entered in the survey date column. The formula I'm using is:
Again, though I'm successfully using nested IF statements elsewhere in the spreadsheet, in this formula using Excel's date functions, I can't figure out where I need to put an additional IF statement in this formula to leave the cell empty if no survey date is entered.
If anyone can point me in the right direction for either of these formulas I'd be most grateful.
The spreadsheet holds data on surveys conducted on where repairs are needed to roads and footpaths. As part of the spreadsheet I have two columns - one that shows the date repair work was completed, and another that displays either "completed" or "repair outstanding" based on whether there's a date in the column that shows the date the repair was completed or not, which I've also got colour-coded using conditional formatting. This is the formula I'm using:
Code:
=IF(ISNUMBER(O4),"Completed","Repair outstanding")
This works fine, except where a survey's been completed but no repair work is required, where because there's no date in the column with the date completed, though no work is needed, it still shows "repair outstanding". There's a further column for the current status of the repair which shows the status of the repair request based on the severity of the repair needed - whether it's urgent and needs completing immediately so is shown as "pending" or not so severe so has been put on a work programme to be completed and is shown as "programmed". This column also shows "completed" for completed repairs, and "repair not required" for those not requiring repairs.
What I'm trying to achieve is changing the formula to check whether this third column shows "repair not required" and leave the cell blank if either no repair's required or there's no data in that cell. In other parts of the spreadsheet I'm successfully using nested IF statements to check the condition of two cells, but for some reason I can't get the same approach to work in this formula using ISNUMBER.
I have a similar problem with another column where I want the financial year to display (e.g. 2017/18), based on the date of the survey. This too is working fine, except where no date is entered, where it shows "1899/00", where I want the cell to remain blank if no date is entered in the survey date column. The formula I'm using is:
Code:
=IF(OR(MONTH(B14)<=3,AND(MONTH(B14)=3,DAY(B14)>=1)),YEAR(B14)-1,YEAR(B14))&"/"&TEXT(IF(OR(MONTH(B14)<=3,AND(MONTH(B14)=3,DAY(B14)<=31)),B14,DATE(YEAR(B14)+1,MONTH(B14),DAY(B14))),"yy")
Again, though I'm successfully using nested IF statements elsewhere in the spreadsheet, in this formula using Excel's date functions, I can't figure out where I need to put an additional IF statement in this formula to leave the cell empty if no survey date is entered.
If anyone can point me in the right direction for either of these formulas I'd be most grateful.