IF AND Nested Formula Help

iando99

New Member
Joined
Apr 9, 2012
Messages
16
Hi Guys & Gurls

thanks for reading this, I'm having a few issues with a current spreadsheet I'm working on, its designed to track inspection frequencies, it was all working fine until the client decided they wanted to track two different industry requirements :(

its set out like this

Cell A1 has the asset build date or 'ground zero' if you like - 01/01/1991

Cell H7 has the client requirement for inspection in days - 365 (every year they'll inspect it)

Cell G7 has the industry best practice for inspection in days - 730 (every 2 years they'll inspect it)

Cell O7 has the Certificate of Conformity date in it - 1/1/10 - (date the asset was built)

Cell P7 has the following formula - =IF(H7="",$A$1,MAX(O7,$A$1))

Cell Q7 is where we record the last inspection date if the client has documentation

Cell S7 is where i'm stuck, it currently reads =IF(H7="","",MAX(Q7,P7)+H7) to give me the date they must next inspect the item, however what i need it to do is

IF H7 is blank use G7
IF BOTH H7 & G7 are blank stay blank

if H7 is used the end of the formula needs to read MAX(Q7,P7)+H7)
if G7 is used it needs to read MAX(Q7,P7)+G7)

hope someone can help, I'm not opposed to hidden cells to do part of the equation if that's the best way around it :)

thank you in advance Ian
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,728
Office Version
2013
Platform
Windows
Ian,

If I understand correctly then try...

=IF(AND(H7="",G7=""),"",IF(H7="",MAX(Q7,P7)+G7,MAX(Q7,P7)+H7))

Hope that helps.
 

Forum statistics

Threads
1,081,578
Messages
5,359,740
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