Sep 19 2023 08:08 AM
Sep 19 2023 08:31 AM
could you be a bit more precise on the exact criteria, it would be easier to provide a good solution (maybe a screenshot etc.)
Generally spoken, the answer would look like this:
So, if the logical test results in TRUE, the hyperlink function will be used. Otherwise something else (e.g. a blank cell, a comment, a value etc.)
Sep 19 2023 08:44 AM - edited Sep 19 2023 09:08 AM
=if(B6=today(),"Unavailable",if(ISNUMBER(MATCH(D9,'Sheet 2',0)),"Full",HYPERLINK([sheet 2],"Book")))
1. If the date entered is the same as today's date, then the activity is "Unavailable".
2. If the date entered is after today:
a. If the time entered matches the time in Sheet 2, then "Full" and no hyperlink
b. If the time entered does not match the time in Sheet 2, then "Book" hyperlinked to Sheet 2
Not sure if it makes sense :(
Thank you very much.
Sep 20 2023 07:52 AM
thanks for the additional information.
The issue is, that even if already the first criteria is met and the formula delivers "unavailable", it creates a invalid hyperlink.
So my proposal would be to create also for "unavailable" and "full" a hyperlink which directs only to the current cell. So in fact, there would be a hyperlink that does nothing.
Maybe try this:
=IF(B6=TODAY(),HYPERLINK("#","Unavailable"),IF(ISNUMBER(MATCH(D9,'Sheet 2'!B2:B20,0)),HYPERLINK("#","Full"),HYPERLINK("#'Sheet 2'!A1","Book")))
Please note, that this formula only checks the time in D9 against the times in range B2:B20, but it does not consider if the date in A2 equals the booking date.