• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scheduling Formula Snafu

IronMike

IronMike

New Member
I am trying to build a scheduling tool for our staff and am pretty far along for a rookie. Maybe this is just too advanced for me. I would like Column B to show the starting time and Column C to show the end of their schedule. In Row 5 the "Start" Cell or B5 should read "7:30 AM" as in Cell E4, and the Row 5 cell C5 should be the end of the shift. In this case it should read 5:00 PM, the end of the shift or Cell X4. Is this doable or just too complicated? Any help would be appreciated! Love the knowledge obtained on this site!

Iron Mike
 

Attachments

Hi, IronMike!
You want to populate columns B:C of worksheet Week1 for each day of the week and employee, but it's missing the employee daily shifts, i.e. the main data. Am I missing something?
Regards!
 
SirJB7,

I want the columns B:C to populate the start time "Start" of the shift indicated by the "X" and the end time "End" of the shift indicated by the last "X." Is that what you are looking for? Is this doable?

IronMike
 
Hi IronMike,

Please place this in B5 and Drag to D5, Press Ctrl+Shift+Enter to run.

=TEXT(INDEX($D$4:$Y$4,,SMALL(IF($D5:$Y5="X",COLUMN($D5:$Y5)),COLUMN(A1))-3),"HH:MM AM/PM")
Click to expand...

..assumed that you have place "x" on start and end marks of the shift.

Regards,
 
@SirJB7
Hi, myself!
So long...
Always arriving late?
Regards!

Hi, IronMike!
I join Faseeh suggestion.
Regards!

@Faseeh
Hi!
I love when you do my job :)
Go on...
Regards!
 
Faseeh said:
Thanks Mike :)
Click to expand...

I seem to have spoke too soon oh great Excel Ninja! Attached is an example. What I need to do is fill in the schedule for the employees for each day. The attached file shows this for the first day.

Employee 1 starts the day at 9:00 AM and finishes the day at 5:00 PM. (each "x" in the cells indicates the scheduled time in 30 minute increments) It totals the hours for the day. But shows the schedule to be "Start" at 09:00 AM and "End" at 09:30 AM. Is there a better way to do this? Maybe reverse engineer this or actually make sit say start at 9:00 AM and finish at 5:00 PM? Am I trying to do something that can't be done?

IronMike
 

Attachments

Hi, IronMike!

It seems as if you and Faseeh are doing different things: Faseeh marks starting and ending time with "X" while you mark all columns from starting to ending time, that's why it doesn't work for you.

Just change columns B:C formulas as follows:

B:
from: =TEXT(INDEX($D$4:$AA$4,,SMALL(IF($D5:$AA5="X",COLUMN($D5:$AA5)),COLUMN(A1))-3),"HH:MM AM/PM")
to: =TEXT(INDEX($D$4:$AA$4,,SMALL(IF($D5:$AA5="X",COLUMN($D5:$AA5)),1)-3),"HH:MM AM/PM")

C:
from: =TEXT(INDEX($D$4:$AA$4,,SMALL(IF($D5:$AA5="X",COLUMN($D5:$AA5)),COLUMN(B1))-3),"HH:MM AM/PM")
to: =TEXT(INDEX($D$4:$AA$4,,LARGE(IF($D5:$AA5="X",COLUMN($D5:$AA5)),1)-3),"HH:MM AM/PM")

Just advise if any issue.

Regards!

PS: BTW, there're a lot of missing times that might cause the formulas to don't work properly if they were in row 4 (1st hour row).
 
I saw that as well. My brother is the Chiropractor. LOL! Not an excel or computer person. I will clean that up once I get the formula working. This site rocks the house! Thak you to all who has helped!

IronMike
 
Hi, IronMike!
Do you mean you can't get the new formulas working? Are any wrong value displayed?
Regards!
 
SirJB7,

I attached the doc. I typed in the formula and it didn't work and then did a cut and paste and still it won't work. I am kinda feeling like a moron. I can twist server and VM infrastructure every which way, but this excel sheet is twisting me up! What am I not doing correctly? Thanks for the help in advance.

IronMike
 

Attachments

Hi, IronMike!

Hi IronMike,

Please place this in B5 and Drag to D5, Press Ctrl+Shift+Enter to run.
Click to expand...

You've just missed that those formulas are array formulas, you you should enter them with Ctrl-Shift-Enter instead of Enter. Just select the cell, press F2 and then Ctrl-Shift-Enter.

Regards!
 
This worked like a charm. I did not know I had to hit F2 before Ctrl-Shift-Enter. I cannot thank all of you enough for your help. It was quick, and direct and fix my issue. I will definitely be using this site and recommend it to any one with an Excel question. You guys rock!

IronMike
 
Hi, IronMike!
Glad to help you, and sorry for the delay, but we'd have to blame Faseeh, he always does this things, we're just getting used to... :P
Regards!
 
You must log in or register to reply here.
Back
Top