• 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.

Highlight the cell range by selecting dropdown

atulrajratna

New Member
Hi all excel gurus,


I have created an Shift Time Manager worksheet where we will come to know about the exact work flow and the work completion flow.


I have uploaded two files, please read the notes and help me.

1st) I need your help in this-

https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!111&parid=root

-If I select shift timing from the drop-down, the cell range should be highlighted, so that we can enter the numbers in that area.


2nd) The original one project-

https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!110&parid=root

-By using this sheet, we will come to know about the workflow, and accordingly we will manage our shifts. I need your "HATS-OFF" suggestion to make it more & more better..


Thanks!


-Atul Rajratna
 
Hi, Atul Rajratna!

I got error messages when trying to download both files. Or the link is wrong, or the public are not granted access to that skydrive folder of yours.

Regards!
 
Hi SirJB7 ,


I too got the same messages ; what I did was select both the files ( by checking the checkboxes by their side ) and clicking on download ; both the files have been zipped into one file which you can download.


Narayan
 
Hi SirJB7 & Narayan,

m extremely sorry! I dont know, why but m also getting the same error.

so I have uploaded a new folder with both files, Please try with this.


https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!112&parid=root


Thanks.

-Atul
 
Good Morning Guys,


Why does this excel file shows Symbol of Power Point when i save it?? :D


Regards,

Faseeh
 
Faseeh,

PowerPoint is the default program for displaying EXCEL files in a browser, I guess. I just changed the default on my download and it came up normally. Some MS Office thing (as I'm using Firefox to download, not IE).


Atul,

Looking at your data, your shifts are always 10 hours long. Due to this, you need to know only the start time, not the end time. Also, since you don't have the times formatted for a computer to understand (5 AM is NOT computerese for 5:00 AM), you'll have to parse those cells (5 AM to 3 PM needs to be 5:00 AM to 3:00 PM), so you'll need either two helper cells to break out the data, or a VBA script to do it.


After you identify those numbers, you can use conditional formatting to highlight the cells.


Do you need help in writing the parser formulas?


Don
 
Hi DonMinter,


How can I use conditional formatting after changing the time-frame from 5AM to 5.00 AM?

or should i change the drop-down list? instead of using shift start timing 5AM, should I use 5.30 AM as work start time?


Thanks!

-Atul
 
Hi Atul,


Got conditional formatting working !!! Can you explain how ur calculating agents counts and productivity? Any formulas for them?


Faseeh
 
Faseeh,


I am done with productivity, it is already there. once I'll get agent count and faxes count in the shift, I'll get the productivity.


I counted the numbers in agents count column manually. I want to count the total agents available in that shift, and total faxes received in that particular shift.


e.g- shift is 5.00 AM to 3.00 AM, so I need a formula which will calculate the number of available agents in selected shift i.e- 3 & number of faxes received in those 10 hrs out of all the day.


Thanks!

-Atul
 
Lets say shift 1 am to 11 am if i need to count for this shift how should i proceed?


I must count for the non blank cells and the max no should be the count for agent? Is it the way?


Faseeh
 
Yes, but counting non-blank cells will also calculate the other shifts number as well. b'coz we have so many shifts in between.


I'm not getting the proper formula actually.

but the results should be like this-


We have 3 agents in selected 1 am to 10 am shift. (Agents count)

The total work we received in the day is 1184. (Faxes Count in a day)

but in 1 am to 11 am shift we received 778 faxes. (Faxes Count in selected shift)

They processed 270 faxes out of 778. (Faxes Count by available agents in selected shift)


Using this result we can get the productivity.

How much work we are getting, how much we can do more, what should be the shift timing & agents availability, who is doing bad and all.. :)


Thanks!
 
Hi,


Lets examine following:

[pre]
Code:
List # 1 (Cells B5: Downward)

Names		Shift
Ashutosh	1 AM : 11 AM
Suhas		2 AM : 12 PM
Santosh		4 AM : 2 PM
Prafulla	5 AM : 3 PM
Husain		6 AM : 4 PM
Sadik		7 AM : 5 PM
Kiran		1 AM : 11 AM

List # 2 (Cell X5 downward)
Shift		Agents
1 AM : 11 AM	3
5 AM : 03 PM	1
4 AM : 02 PM	1
5 AM : 03 PM	2
[/pre]
From List # 1, the No. of operators between:

- 1 AM : 11 AM are 02 why are you taking them 03?

- Remaining all are 01 for each shift mentioned? Is it correct?


Regards,

Faseeh
 
Hi Atul Rajratna,


See this file whether i am moving in right direction? Kindly see my last post as well.

http://dl.dropbox.com/u/60644346/QUERY.xlsx


Regards,

Faseeh
 
Hey Faseeh,


m so sorry for the delay. & Its working yaar, great.. Thanks, Thanks a lot.


now agent count(2) and completed faxes count(171) is done. but what about the total count of faxes we received in the shift? i.e 778 (sum of faxes in C3:S3 range)


and how to make conditional formatting to highlight the shift cell range?


Thanks,

Atul
 
Faseeh,


Yeah, I copied drop-down list of shifts to your sheet, and the cell ranges are highlighting as per shift. Its fantastic.


The formula-

=AND(D$6>=TIMEVALUE(LEFT($B9,4)),D$6<=TIMEVALUE(MID($B9,FIND(":",$B9)+1,7)))

is gr8! gr8!! gr8!!!


Hope it will work in 2003, How did you do that?


Thank you so much Sir!

Atul
 
Hi, Atul Rajratna!

Well, everything's done here. Glad to see you could solve it. Welcome back whenever needed.

Regards!
 
Hi Atul,


Nice to see your reply and am glad that things are working as they were supposed to! BTW did you used 'Sir' for me or for 'SirJB7' ;P ??


Will you please check the last link again? I think the last part has also been 'Fixed' It is giving the total of faxes received in a shift? Please check. :)


...and thanks for the compliments!


Take Care & Enjoy,

Faseeh
 
Faseeh,


Its showing the number of faxes completed by agents in that shift. not received numbers.

Result(K17) should be- 778 faxes as per your sheet(range C3:L3 - 1.30 AM to 10.30 AM).

How to do that?


Atul
 
Hi Atul,


I was taking 11 Hrs shift by mistake kindly check this one:

http://dl.dropbox.com/u/60644346/Atul%20Rajratna_ShiftQuery.xlsx


Regards,

Faseeh
 
Back
Top