Forum Discussion
IF function
Hello.
I have used the IF function to add 16 weeks to date 2 if it is not empty and date 1 if it is empty. However, if both columns A and B are blank then I get the date 20/04/1900. Does anyone know why this is? Also, does anyone know how to edit the formula so that column C stays blank if A and B are empty rather than getting the date 20/04/1900?
Thanks in advance for any help.
Hannah
=IF(AND(A2="",B2=""),"",IF(B2<>"",B2+7*15+6,A2+7*15+6))
You can use this formula for the intended result.
=IF(B4<>"",B4+7*15+6,A4+7*15+6)
This formula returns 20.04.1900 or 111 in cell C4. B4 isn't <>"" therefore the formula calculates A4+7*15+6. Since A4 is 0 the calculation is 0+7*15+6 and the result is 111. The format of cell C4 is date and 20.04.1900 is the 111th day since 01.01.1900.
For example today's date 21.11.2023 in number format would be 45251 which means it's the 45251st day since 01.01.1900.
2 Replies
=IF(AND(A2="",B2=""),"",IF(B2<>"",B2+7*15+6,A2+7*15+6))
You can use this formula for the intended result.
=IF(B4<>"",B4+7*15+6,A4+7*15+6)
This formula returns 20.04.1900 or 111 in cell C4. B4 isn't <>"" therefore the formula calculates A4+7*15+6. Since A4 is 0 the calculation is 0+7*15+6 and the result is 111. The format of cell C4 is date and 20.04.1900 is the 111th day since 01.01.1900.
For example today's date 21.11.2023 in number format would be 45251 which means it's the 45251st day since 01.01.1900.
- OliverScheurich thank you so much that was really helpful!
