Creating a macro for date range.

[Item Manager]

I have a perpetual calendar in excel that updates each month by simply
changing the month and year to reflect most current month and year. For each
month selected, formulas have been created to automatically insert 8hrs in a
cell during weekdays only, but not including weekends. The timeframe
captured is always the 1st-15th and sumed for total hrs and 16th-30th repeats
the same process. Under the required cell with the 8hrs, I put the actual
hrs worked for that day which is less than the required 8hrs or more. For the
timeframe ending on the 15th of every month, I'm using this formula
=IF((SUM(C$14-$B$3))<15,8,"") for the required 8hrs that automatically
populates the cell. The days are fomatted Sun-Sat. I said all this to ask
this question: 1) how do I create a formula that will add up my hrs
manually inserted, only from the 1st-15th, and not capture remaining hrs on
16th, 17th etc. if on the same row of 15th? Please keep in mind I have a
perpetual calendar 2) how do I create a macro button that will delete all
data from 1st-15th timeframe, no matter what month I change the calendar to?
3) how do I setup my perpetual calendar to post the name of US holidays per
month? The formula for the calendar days are =(WEEKDAY($B$3)=2)*B3+(B6>0)+B6
I don't know how to add the holiday to applicable day. Please help. Thanks.

 

[Nigel]

I can only reply in principal as describing a workbook by references and
formula is not the easiest to decipher!

1. To add up specific days you could extract the day number using the DAY
function from each reference date, this will give you a numerical value to
use to control your summation. SUMIF(yourDAYSrange,
"<=15"),yourRangetoSum)

2. The macro you require could also use the above technique, looking for the
DAY value from 1 to 15, DAY will ignore month. But to supply code you need
to be more specific about what DATA you wish to delete and where it is
stored.

3. I have no idea what your formula is doing as it has cell references that
do not mean anything to me. In principal you could set up a lookup table
somewhere in your workbook (or in an external workbook) that contains ALL
holiday dates and names. Use the VLOOKUP function for each date in your
calendar to test if it is a holiday or not.

Regards