Home

How to add 1/2 days on a Attendance Tracker?

%3CLINGO-SUB%20id%3D%22lingo-sub-321115%22%20slang%3D%22en-US%22%3EHow%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321115%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20am%20using%20Microsoft%20attendance%20tracker-%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftemplates.office.com%2Fen-us%2FEmployee-attendance-tracker-TM02809209%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftemplates.office.com%2Fen-us%2FEmployee-attendance-tracker-TM02809209%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20track%201%2F2%20days%20in%20both%20vacation%20and%20sick%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-321115%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1086568%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1086568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20very%20much.%26nbsp%3B%20your%20solution%20worked%20beautifully.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321652%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321652%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%20%22s%22%2C%20%22v%22%2C%20etc.%20you%20may%20type%20%220.5s%22%2C%20%220.8v%22%2C%20etc.%20and%20instead%20of%20COUNTIF%20use%20formula%20as%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(VALUE(%220%22%26amp%3BSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(%24F6%3A%24CQ6%2C%22p%22%2C%22%22)%2C%22v%22%2C%22%22)%2C%22s%22%2C%22%22))*(RIGHT(%24F6%3A%24CQ6%2C1)%3D%22S%22))%2BSUMPRODUCT(--(%24F6%3A%24CQ6%3D%22S%22))%3C%2FPRE%3E%0A%3CP%3Ethis%20one%20is%20for%20sick%20leaves%2C%20another%20two%20are%20similar.%3C%2FP%3E%0A%3CP%3EPlus%20in%20conditional%20formatting%20rules%20change%20formulas%20like%3C%2FP%3E%0A%3CPRE%3E%3DF6%3D%22S%22%0A%0Aon%0A%0A%3DRIGHT(F6%2C1)%3D%22S%22%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20in%20attached%20file%20(it's%20done%20for%201st%20quarter%20only)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321420%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321420%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Sergei%2C%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I'm%20using%20a%20different%20tracker%20than%20the%20one%20you've%20suggested%20a%20solution%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20template%20we're%20using%20is%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftemplates.office.com%2Fen-us%2FEmployee-attendance-tracker-TM02809209%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Ftemplates.office.com%2Fen-us%2FEmployee-attendance-tracker-TM02809209%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20suggest%20a%20solution%20for%201%2F2%20days%20for%20this%20tracker%3F%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321195%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321195%22%20slang%3D%22en-US%22%3E%3CP%3EPerhaps%20the%20easiest%20way%20is%20to%20add%20one%20more%20column%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20578px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69103i1D699004FFEA0975%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20the%20formula%20in%20Days%20column%20change%20on%3C%2FP%3E%0A%3CPRE%3E%3DNETWORKDAYS(%5B%40%5BStart%20Date%5D%5D%2C%5B%40%5BEnd%20Date%5D%5D%2ClstHolidays)%2B%5B%40Adjustment%5D%3C%2FPRE%3E%0A%3CP%3ECorrect%20format%20of%20these%20two%20columns%20after%20that.%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1086580%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%201%2F2%20days%20on%20a%20Attendance%20Tracker%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1086580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F268649%22%20target%3D%22_blank%22%3E%40sunadmin%3C%2FA%3E%20%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
sunadmin
New Contributor

Hello!

I am using Microsoft attendance tracker-

https://templates.office.com/en-us/Employee-attendance-tracker-TM02809209

 

How do I track 1/2 days in both vacation and sick?

 

Thank you very much!

5 Replies
Highlighted

Perhaps the easiest way is to add one more column here

image.png

and the formula in Days column change on

=NETWORKDAYS([@[Start Date]],[@[End Date]],lstHolidays)+[@Adjustment]

Correct format of these two columns after that.

Please see attached

Highlighted

Hello Sergei,

Thank you for your reply.

 

However, I'm using a different tracker than the one you've suggested a solution for.

 

The template we're using is:

https://templates.office.com/en-us/Employee-attendance-tracker-TM02809209

 

Can you please suggest a solution for 1/2 days for this tracker?

Thank you!

Highlighted

Hi,

 

Instead of "s", "v", etc. you may type "0.5s", "0.8v", etc. and instead of COUNTIF use formula as

=SUMPRODUCT(VALUE("0"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($F6:$CQ6,"p",""),"v",""),"s",""))*(RIGHT($F6:$CQ6,1)="S"))+SUMPRODUCT(--($F6:$CQ6="S"))

this one is for sick leaves, another two are similar.

Plus in conditional formatting rules change formulas like

=F6="S"

on

=RIGHT(F6,1)="S"

Please check in attached file (it's done for 1st quarter only)

Highlighted

@Sergei Baklan  Thank you very much.  your solution worked beautifully.

Highlighted

@sunadmin , you are welcome, glad to help

Related Conversations