Hi all,
Background: I’m trying to create a spreadsheet that, among other things, indicates when an employee’s shift starts. On that spreadsheet, I have to indicate whether the employee is assigned to general work (which I mark with an “X”) or whether they have to work at the front desk (which I mark with a “Front”).
Where I’m at Now: I’ve adapted (i.e., stole) much of the attached spreadsheet from Excel Ninja Faseeh’s post on September 4, 2013 on the topic of Scheduling Formula Snafu. It works terrific.
My Problem: The spreadsheet indicates the shift start time (and end time) but only if you enter an “X” in the applicable cell. I’m trying to get it so that it would indicate the shift start time if I entered an “X” or “Front” in the appropriate cell.
So here’s the formula for Cell B5 (which is the start time on Monday for one of our employees):
{=TEXT(INDEX($D$4:$V$4,,SMALL(IF($D5:$V5="X",COLUMN($D5:$V5)),1)-3),"HH:MM AM/PM")}
I tried adding an OR to the formula before the IF but that didn’t work for me (likely due to the fact that I don’t fully understand the formula.
My Goal: To have the attached spreadsheet show the correct starting time whether I enter “X” or “Front” in the applicable cell.
Any help here would be much appreciated. Thank you.
Mark
Background: I’m trying to create a spreadsheet that, among other things, indicates when an employee’s shift starts. On that spreadsheet, I have to indicate whether the employee is assigned to general work (which I mark with an “X”) or whether they have to work at the front desk (which I mark with a “Front”).
Where I’m at Now: I’ve adapted (i.e., stole) much of the attached spreadsheet from Excel Ninja Faseeh’s post on September 4, 2013 on the topic of Scheduling Formula Snafu. It works terrific.
My Problem: The spreadsheet indicates the shift start time (and end time) but only if you enter an “X” in the applicable cell. I’m trying to get it so that it would indicate the shift start time if I entered an “X” or “Front” in the appropriate cell.
So here’s the formula for Cell B5 (which is the start time on Monday for one of our employees):
{=TEXT(INDEX($D$4:$V$4,,SMALL(IF($D5:$V5="X",COLUMN($D5:$V5)),1)-3),"HH:MM AM/PM")}
I tried adding an OR to the formula before the IF but that didn’t work for me (likely due to the fact that I don’t fully understand the formula.
My Goal: To have the attached spreadsheet show the correct starting time whether I enter “X” or “Front” in the applicable cell.
Any help here would be much appreciated. Thank you.
Mark