If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
Also, to ensure that your UserName is registered, please ensure that you have confirmed your e-mail address by clicking on the link that will be sent to your address. Users who do not confirm their e-mail addresses will be able to browse the forum, but not able to download any content

The code seems to be working fine..i need normal OT and Holiday OT to appear exactly in cell G34 and J34
Can i use the same code for every month and if yes what changes i need to do from my end
I have around 800 sheets ...will the code work on all sheets in one go

Further to the TWO codes you now have from US .......... :)

I think you can see what part of the code determines where the output goes

Code:

'3f) Paste out final Totals and days to current WorksheetLet wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a dayLet wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a dayLet wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34'3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed

Yes i actually forget to tell you about the absent days.What i mean "Every month we consider of 30 days , if the month is of 30 or 31 or like 28 days Feb all these months we take it as 30 days only.
Total no. of days is the no. of days the person worked plus the holidays(holiday is deducted if the person does not come the day before and after the holiday)

Hi Nelson,
So I assume you want to use the code that puts formulas in and adds a help column??? ( The second code , Sub IJAdjustKAddTotalAllWorksheet() )
( I see you have changed the row of the cells used to display the output to 34 )

So possibly we can use column L for the help column ?

_....................

In your after file, ( "File 2 After.xlsx" ) , you have given 26 Working days for Worksheet “121”:Using Excel 2007 32 bit

......(holiday is deducted if the person does not come the day before and after the holiday).....

I do not understand
_2a) Please give example to illustrate this

_ 2b) (i) What if the above situation arises, and the Holiday is the first or last day of the month ??? - Would you check the month before and after to see if a day was not worked???
_ 2b) (ii) You will need some external record set at the end of each month which is accessible in the next month to check each sheet for an absence on the last day of all Months.
_.....

You must make sure that your test data in Before and After illustrates all possible scenarios( and that it is correct ! )

ExcelFox is Not Associated With Microsoft®. ExcelFox Retains the Rights to ALL Posts and Threads. Fluent is a trademark of Microsoft Corporation and the Fluent user interface is licensed from Microsoft Corporation.