Sorting "" properly for date formatted column

Having an issue with sorting dates properly.

Formula in each cell of column P is:
=IF(O6="","",INT(O6)+120)

*have also tried
=IF(ISBLANK(O6),"",INT(O6)+120)

The issue is when filtering column P I can only sort a-z as opposed to oldest to newest

Ran test on it [ =ISTEXT(P6) ] and find that if there is a date in it result is false and if not, "" returns as True.

So the issue is that Excel sees the blank "" as text and tries to filter accordingly. Is there a solution to this or am I stuck with 1/1/1900 instead of blanks if I want to be able to filter properly?

Your problem is not at all clear as to what you are dealing with. It could be as simple as =IF(O6="","",O6+120) for column P if the data in column O are real dates.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Edited sheet to remove sensitive data so what I referred to as column O is now J and P is now K

The issue is I want to be able to sort the sheet by the dates in column k oldest to newest and I only have the option of A -> Z

I have copied the formula quite a ways down in the spreadsheet as this is intended to be a shared sheet and other users will not have the know how to copy that information down into each new row.

There is something strange with column K. Insert a new column and format as Date and enter =IF(J2="","",J2+120) in row 2 and fill down. Delete the old column K and title the new column.

Change "" to 0 and hide the 0 using formatting or set the workbook to not show 0 values in File>Options.

Or you could sort by DATE OF HIRE and you would get the same results as you would if you sorted chronologically on PAYMENT DATE

I see what is happening. I left a few blank rows and filled the formula down....the result was that the filter reverted to A-Z type sorting. If I entered a date after the blank area, the filter again reverted back to Oldest to Newest again. It is an oddity that the blank cells if not "bracketed by dates" (date entered after the blanks) will be treated as text.

I would convert all the data to a table and not fill any more rows than you have data for. All formulae will fill as you add more rows.