Hello Everyone,
I need help with an Excel formula.
I am currently working on a client availability sheet. Here is how the data is structured: dates are listed in columns and times are listed in rows. Also, I have placed 2.0 to indicate the client's available timeslot during that day. Here's how the data looks like and the output I need help with:
I have created a formula that finds the available timeslot but, I only require the first and the last timeslot of that particular day.
This is the formula: =TEXTJOIN(" - ", TRUE, IF(INDEX($C$4:$P$33, MATCH(PN$22,$B$4:$B$33, 0), 0) = 1, TEXT($C$2:$P$2, "hh:mm AM/PM"), ""))
The output this formula giving me:
$C$4:$P$33 - Data reference i.e. 2.0
PN$22 - Drop-down date
$B$4:$B$33 - Date reference
$C$2:$P$2 - Time reference
Is it possible to achieve this output?
Thank you.
I need help with an Excel formula.
I am currently working on a client availability sheet. Here is how the data is structured: dates are listed in columns and times are listed in rows. Also, I have placed 2.0 to indicate the client's available timeslot during that day. Here's how the data looks like and the output I need help with:
| 09:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | ||
01-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | |||||||||||
02-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | |||||||||
03-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | ||||||||
04-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | ||||||||
05-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | ||||||||
06-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | ||||||||
07-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | |
08-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | |||||
09-May-24 | 2.0 | 2.0 | 2.0 | 2.0 | |||||||||||
| Required Output | |||||||||||||||
| Available time slot | |||||||||||||||
| Date drop down | 01-May-24 | From 16:00 to 19:00 | |||||||||||||
| Similarly for other dates too | |||||||||||||||
02-May-24 | From 16:00 to 21:00 | ||||||||||||||
| 03-May-24 | From 09:00 to 15:00 |
I have created a formula that finds the available timeslot but, I only require the first and the last timeslot of that particular day.
This is the formula: =TEXTJOIN(" - ", TRUE, IF(INDEX($C$4:$P$33, MATCH(PN$22,$B$4:$B$33, 0), 0) = 1, TEXT($C$2:$P$2, "hh:mm AM/PM"), ""))
The output this formula giving me:
| 04:00 pm - 05:00 pm - 06:00 pm - 07:00 pm - 08:00 pm - 09:00 pm - 10:00 pm |
PN$22 - Drop-down date
$B$4:$B$33 - Date reference
$C$2:$P$2 - Time reference
Is it possible to achieve this output?
Thank you.
