Solved

Scrub also for select days off and select holidays

Posted on 2015-01-05
17
66 Views
Last Modified: 2015-01-10
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
Comment
Question by:frugalmule
  • 10
  • 7
17 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility

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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Still waiting for your clarification in here.
gowflow
0
 

Author Comment

by:frugalmule
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility

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
 

Author Comment

by:frugalmule
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility

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
 

Author Comment

by:frugalmule
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
wow .... Typical salvery work !!!

Am impressed, r u listed in guiness or not yet ???
gowflow
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:frugalmule
Comment Utility
Amazing!!

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

Thank you so much for your help.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
 

Author Comment

by:frugalmule
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:frugalmule
Comment Utility
0
 

Author Comment

by:frugalmule
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now