IF function formula to specify time based on day of the week

[rylan.browne]

I have a list of the days of the and a departure(or not)

I need a formula (without requirement for lists) that specifies the correct departure time based upon the day of the week.

Therefore, as per attached example, as cell E1 is showing 'Wed' then the time of 07:30 should be displayed in E3.

Can a formula be created using E1 as the info cell with a IF function calculation in E3 with all the required options, i.e. days of week/times within the formula?

[AliGW]

If it's as your example, then this will do it:

=INDEX($B$1:$B$7,MATCH($E$1,$A$1:$A$7,0))

Format the result cell as time.

[rylan.browne]

Thank you for your prompt response.

This works perfectly, however, the list in column A had been formatted to the Date format of ddd from this weeks date range.

This then causes a problem when I get into next week as the result of the calculation returns N/A

Is there any way that a formula will work if the cells A1:A7 are in text format and not date format?

[Glenn Kennedy]

Post a sample sheet showing EXACTLY what you mean.

[rylan.browne]

I have attached the file

Thanks

[Glenn Kennedy]

Thanks. Clear now.

=INDEX($B$1:$B$7,MATCH(TEXT($E$1,"ddd"),$A$1:$A$7,0))

[rylan.browne]

Thank you, it works perfectly.

[rylan.browne]

Hi, just one last thing . . .
If there is no time in column B, then the calculation returns 00:00. Is there anyway this can be returned as blank?

[Glenn Kennedy]

can 0:00 EVER be a correct answer? If not:

=IFERROR(1/(1/INDEX($B$1:$B$7,MATCH(TEXT($E$1,"ddd"),$A$1:$A$7,0))),"")

[HansDouwe]

A little bit another approach (also works in non-English speaking countries)

Try in Cel A1: =WEEKDAY(MOD(SEQUENCE(7),7)+1)
Try in Cel E3: =IFERROR(1/(1/INDEX(B1:B7,MATCH(WEEKDAY(E1),A1#,0))),"")
Everything seems the same!

By the way: Instead of "IFERROR(1/(1/" it is also possible to change the format of CEL E3 to "hh:mm;;" In that case, 0:00 is also made invisible.