Feb 22 2023 03:53 AM
Hi team
I am in need of some assistance regarding a function in Excel.
I am looking for a way to automatically update a travel date that has already expired in a cell to the next valid date based on information in another cell. Specifically, if a travel date has already passed (e.g. February 20th, 2023), I want to be able to automatically update the cell with the next valid travel date (e.g. March 30th, 2023) that is already specified in another cell.
Is there a function that can achieve this? I would appreciate any help or guidance on this matter. The update can be made on the same sheet or a different sheet, whichever is easier.
Feb 22 2023 04:25 AM
=IF(C3<TODAY(),XLOOKUP(C3,E3:E12,E3:E12,,1),"")
If you work with Office 365 or Excel 2021 you can try this formula. If the planned travel date isn't in the past an empty cell is returned instead of the next possible travel date.
Feb 27 2023 12:52 AM
Thanks for your reply, appreciate it, but the formula not working for me as you can see the attached picture
Feb 27 2023 02:15 AM
=IF($C$3<TODAY(),SMALL(IF($E$3:$E$12>TODAY(),$E$3:$E$12),1),"")
I assume the error is because you don't work with Office 365 or Excel 2021. An alternative could be above formula which works in my desktop Excel 2013 version. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.