Excel Formula and New Year

[salameda]

I have a spreadsheet that turns cells pink when they are more than 30 days between the In and Out dates entered. Since we have changed from 2022 to 2023 the cells are now showing a negative number. I have included the formula in the cell as well as an image of the spreadsheet. The first two cells are pink as they are over 30 days between dates and the third cell is not pink since it is only 29 days. The fourth cell should be pink and show 62 days but it shows -303.

Thank you for any assistance!

=IFERROR(IF(ISBLANK([@Name]),"",IF(ISBLANK([@[Date In]]),"",IF(ISBLANK([@[Date Out]]),"",TEXT([@[Date Out]],"MM/DD")-TEXT([@[Date In]],"MM/DD")))),"")

 

[Fluff]

How about

=IF([@Name]="","",IF(OR([@[Date in]]="",[@[Date out]]=""),"",[@[Date in]]-[@[Date out]]))

 

[salameda]

This did turn the fourth cell into the correct number of 62, however all of the cells became a negative number.

 

[Fluff]

Not for me it doesn't

Fluff.xlsm
	Q	R	S
3	Date out	Date in	Days
4	08/11/2022	13/12/2022	35
5	08/11/2022	09/01/2023	62
6	08/11/2022	22/12/2022	44
7	23/11/2022	22/12/2022	29
Master

Cell Formulas
Range	Formula
S4:S7	S4	=IF([@Name]="","",IF(OR([@[Date in]]="",[@[Date out]]=""),"",[@[Date in]]-[@[Date out]]))

 

[salameda]

Not for me it doesn't

Fluff.xlsm
	Q	R	S
3	Date out	Date in	Days
4	08/11/2022	13/12/2022	35
5	08/11/2022	09/01/2023	62
6	08/11/2022	22/12/2022	44
7	23/11/2022	22/12/2022	29
Master

Cell Formulas
Range	Formula
S4:S7	S4	=IF([@Name]="","",IF(OR([@[Date in]]="",[@[Date out]]=""),"",[@[Date in]]-[@[Date out]]))

That is so odd. I tried it again and got a negative number again. Not sure what I'm doing wrong.

 

[Fluff]

In that case can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.