I need the help and patience of a real expert here

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
I've got an excel sheet that tracks vacation. I've put a great deal of work into it with many thanks to the guidance of

countless others on this forum.

Other departments at my work have heard and asked for this handi-work ... and with their requests have also come requests

for more functionality. (What have I done?)

The latest change to my "vacation tracker" is well over my head ... and I wish to put a plea out there to a real expert who

knows VBA with whom I can share my file with and get help with.

I can try to describe my delimma here:

The first column a user enters a code:
These are codes for "whole days" of vacation.
CO (Carry Over from last year)
LV (Lee Vacation)
PD (Personal Days)
ED (Earned Days)

Another set of codes represent "half days" of vacation.
CH (Half Day from Carry Over category)
LH (Half Day from Lee Category)
PH (Half Day from Personal Days category)
EH (Half Day from 'Earned Days' category)

The last set of available codes represent vacation taken "Hourly"
HC (Hourly subtraction from 'Carry Over')
HL (Hourly subtraction from 'Lee Category')
HP (Hourly subtraction from 'Personal Day' category)
HE (Hourly subtraction from 'Earned Days' category)

In the Next Three columns are: Start Date; END Date; and TOTAL
So, after a couple of months, entries might read like this:

CO 6/10/06 6/15/06 6
PD 6/20/06 6/25/06 5
PH 6/29/06 6/29/06 .5

Now ... fast forward down to the bottom of the sheet, the numbers in the FOURTH column will be tallied and subtracted from

within the respective category (based on the code in the first column).

So:
(CO) Carry Over Days allowed: 15 .... Used 6 .... Remaining: 9
(PD) Personal Days allowed: 6 .... Used 5.5 .... Remaining: .5
(LV) Lee Vacation allowed: 20 .... Used 0 .... Remaining 20

I've got everything working perfectly ... except the whole Hourly mess.

What I wish is:
When a person enters any of the hourly codes (HC, HL, HP, HE), a user box prompts the the number of hours to charge ...

then fills it into the fourth column) The user can then manually fill in the dates in the second and third column to

represent when the vacation is taken.

My problem gets stickier as some people are union members and others are not. This means that a workday is varying lengths.

So I've got a VARIABLE box at the bottom of my excel sheet where the number can be changed for each employee. But I would

need that number included in the calculation.


Here's what a couple of columns should look like:
HP 7/1/06 7/1/06 2 Hrs


At the bottom totals row for that category it should read:
PD Allowed: 7 .... Used 5.5 Days 2 Hours .... Remaining 1 Day 5.5 Hours

(The HP and PH categories are also subtracted from same category as (PD Personal Days)



Oh, it's confusing. Is there somebody who can lift me out of the hole I've dug? I would be in your debt.

Thank you
Kevin. :oops:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Kevin

The following sheet event should bring up an input box whenever the first character entered into a cell in column A is "H". It will put the output into column D.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 And Target.Column = 1 And UCase(Left(Target, 1)) = "H" Then
    Target.Offset(0, 3).Value = Application.InputBox(prompt:="Enter number of hours", Type:=1)
  End If
End Sub

Can't fathom enough of the rest to try to work out the formulas for the calculation tho...

Tony
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
Big help

That's fantastic! Thank you for guiding me in the right direction.

Cheers to you my friend.

Kevin :biggrin:
 

Forum statistics

Threads
1,141,591
Messages
5,707,277
Members
421,499
Latest member
Dpbj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top