Excel Clarification

[Thasthahir]

Hi,

Have a clarification in excel. I have creating a 2018th year template file now. It is related to hours Calculation.

Concept is : Leader will update users extra working hours in a sheet. As per company policy all users should be extend 8 hours per month.
If any one not extend 8 hours per month balance hours should be automatically added (Carry forward ) to next month. if user did more than 8 hours extra hours no need to carry forword to next month. can you please help me? Thank you...


For eg: if user A did 4 hours OT in jan month. as per policy he should spend 8 hours. balance 4 hours shouild be add in feb month
now feb month should reads 12 hours

Regards,
Mohideen Thasthahir

[werafa]

this sounds like an 'If, Then, Else'

This example of the logic is not in formula or VBA syntax - use it to understand (or modify) what is needed
if PrevMonthHours is less than 8, then CurrentMonthHours = 8-PrevMonthHours + 8, else CurrentMonthHours = 8

the formula would be
=if (A1 < 8, 8-A1+8,8)

[Thasthahir]

this sounds like an 'If, Then, Else'

This example of the logic is not in formula or VBA syntax - use it to understand (or modify) what is needed
if PrevMonthHours is less than 8, then CurrentMonthHours = 8-PrevMonthHours + 8, else CurrentMonthHours = 8

the formula would be
=if (A1 < 8, 8-A1+8,8)


**************************************

Hi,

Could you create excel and send me the same logic data. because it it not working.

Already i have created tracker like the below



ID
OT
JAN ot hrs
BAL
FEB ot hrs




001
8
4
=B2-C2
8




002
8
5
=B3-C3
6




003
8
10
=B4-C4
4




004
8
15

=B5-C5
1




005
8
2
=B6-C6
14





As per the above table
ID 001 did 4 hour OT in jan maonth. but he should did 8 hours . balance 4 hours + Feb month Ot8=12hours. he should did OT 12 hours ot for feb month

ID 005 did 15 hours in jan month. next month should be read 8 hours. company will pay amount for extra 7 hours

Can you please help me?

[werafa]

ok, this needs more complex logic due to the additional rules.

try 'if (AND(BAL < 8, BAL > 0), BAL + 8, 8)' - put this in FEB ot hrs.
confirm I've used correct syntax for the and statement if you hit an error. this should resolve to true if both statments are true

[SamT]

Feb OT Hours = OT+OT-Min(JanOT ,OT)
Cell E2 Formula = B2+B2-Min(C2, B2)

Mar OT Hours = OT+OT-Min(FebOT ,OT)
Cell F2 Formula = B2+B2-Min(E2, B2)

Copy and Paste or Fill Right or Fill Down Formula, from Feb OT Formula, with no BAL columns
=($B2*2)-MIN(C2,$B2)