Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

Scrub also for select days off and select holidays

Thank you Goflow!

I need to build-in a scrub for selected days off and selected holidays.  This will hopefully be such that the only days on the report should be where the employee was logged off when they were not scheduled to work.  Sorry goflow for not having included this in the original request.

This question  is an extension from this post http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28590451.html#a40531755with the TimeCalc.xlsm document in it.

Assistance is greatly appreciated.
0
frugalmule
Asked:
frugalmule
  • 10
  • 7
1 Solution
 
gowflowCommented:

I need to build-in a scrub for selected days off and selected holidays.

Do not understand what you mean pls clarify what is requested by an example.
gowflow
0
 
gowflowCommented:
Still waiting for your clarification in here.
gowflow
0
 
frugalmuleAuthor Commented:
What I was talking about adding in the new question I posted was the ability to calculate hours not worked if the person did not log in at all on a scheduled shift.  Also, the columns for your calculation are titled ln and Out where they should actually be titled Offline Start, and Offline End.  Also, if it was an approved company holiday, then offline hours do not need to be calculated on those days.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:

the ability to calculate hours not worked if the person did not log in at all on a scheduled shift

What do you mean ? The data you posted had examples like this ? You mean to say when days jump like 12/8 then 12/10 we should calculate that the person was off on 12/9 although there is no record ?


Also, if it was an approved company holiday, then offline hours do not need to be calculated on those days.

Where do we get this offline holiday from ? I can if you want add a sheet that we call holiday and you plug in all the holiday dates of the year then no problem I can link the formula to skip those days.

Waiting for your clarification
gowflow
0
 
frugalmuleAuthor Commented:
YES "Add a sheet that we call holiday and you plug in all the holiday dates of the year then no problem I can link the formula to skip those days."

If days jump and there is no record of having logged-in for their shift, then we do want to keep a record of that, provided they were not off during those days.  We need a way to eliminate approved holiday 'dates' but also eliminate any calculation on 'days' where being off is generally expected.  One employee might be off on Tues and Wed, where another might be off on Sat and Sun.  Therefore, we need a way to not only specify the shift, but also specify what the days off are for a particular person.
0
 
gowflowCommented:

Therefore, we need a way to not only specify the shift, but also specify what the days off are for a particular person.

Can it be more than 1 day per week and will it be always these days ? like Tue and Sat or Mon and Sat Sun ... or it will be depending on dates ?

if more then 1 day per week and always these same days then will create a list for all 7 days mon ... sun and then you choose the days off by highlighting them and then run the macro and will take this into consideration together with the holidays.

Now for holidays the way I see it is simply a list in Col a of all dates that are holidays. This you can create a sheet and plugin from Jan 1 till Dec 31 all known holidays incl when the office is closed Sat Sun ... ??

gowflow
0
 
frugalmuleAuthor Commented:
It will always be those days and it is two consecutive days per week.  It is not date dependent.  Your suggested solution in paragraph two sounds perfect.  The office is never closed, except on Christmas Day.  It is a 24 hour operation.
0
 
gowflowCommented:
wow .... Typical salvery work !!!

Am impressed, r u listed in guiness or not yet ???
gowflow
0
 
gowflowCommented:
back to our subject.

Here is your request. I added 3 columns at the end together with a listbox containing all days of the week. You can select 1 or several if they are not following each others make sure you press CTRL when you click on a new one to highlight.

I added a button Clear Days off to reset the listbox.

Please run it and I appreciate your comments as to the whole INCLUDING the total !!!!

gowflow
TimeCalc-V02.xlsm
0
 
frugalmuleAuthor Commented:
Amazing!!

A couple of little tweaks if you can http://screencast.com/t/Tna5kFRz.

Thank you so much for your help.
0
 
gowflowCommented:
pls add the screenshots in here by clicking on the last icon to the right in the window where you input the thread.
gowflow
0
 
gowflowCommented:
ok got the video rather than the 'screenshots' that you called so. Will see for the details and revert. Meantime it is not clear the last column about the hours what do you want ? the shift being 9:30 till 6:00 then the total hours are 8:30 don't understand your comment about the 30 minutes !!!

The rest will seek to accommodate.
gowflow
0
 
gowflowCommented:
ok here it is with the amendments requested. PLs check it out.
gowflow

PS I have arranged for that column Hours to be HH:MM I removed the seconds that were 00 anyway and could be confusing.
TimeCalc-V03.xlsm
0
 
frugalmuleAuthor Commented:
It's absolutely perfect.

Now I need a way to show how much money something like this would save the company, and propose a path forward regarding having Avaya feed into an online database automatically.

1.  How much time per employee does it take to do this manually and daily by eyeballing it?
2.  How much money at $10.80 per hour for each of avg 200 employees per day is missed by cutting corners on the reporting?
3.  How much time in productivity is lost because supervisors have only time for reporting, and less time to coach employees?

Those kinds of questions would be nice to show some calculations for in another tab, perhaps called, COST - the real cost of this disconnected process.
0
 
gowflowCommented:
Absolutely and be my gest in closing this question and asking a related one with all the specifics you need and will be glad to assist if no one else beat me to it !!!

gowflow
0
 
frugalmuleAuthor Commented:
0
 
frugalmuleAuthor Commented:
With this one, we are not taking into account missed days if there is no new login information.  I have one record where the person's last day to login was 1/1 but the report should show the missed days, so long as it isn't today.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now