SOLVED

Sharepoint count workdays with ISBLANK

%3CLINGO-SUB%20id%3D%22lingo-sub-3220755%22%20slang%3D%22en-US%22%3ESharepoint%20count%20workdays%20with%20ISBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3220755%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20the%20sharepoint%20platform%20and%20currently%20working%20on%20a%20project%20where%20it%20will%20count%20the%20number%20of%20days%20from%20%22Start%20Date%22%20to%20%22End%20Date%22%20excluding%20weekends%20and%20if%20the%20End%20date%20ISBLANK%2C%20it%20should%20calculate%20the%20date%20TODAY.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20can%20help%20and%20thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3220755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3220958%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20count%20workdays%20with%20ISBLANK%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3220958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319715%22%20target%3D%22_blank%22%3E%40ash052621%3C%2FA%3E%26nbsp%3Bto%20get%20you%20started%20the%20formula%20for%20the%20number%20of%20working%20days%20between%20the%20%3CSTRONG%3EStartDate%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EEndDate%3C%2FSTRONG%3E%20columns%20is%20as%20follows%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3D(DATEDIF(%5BStartDate%5D%2C%5BEndDate%5D%2C%22D%22))-INT(DATEDIF(%5BStartDate%5D%2C%5BEndDate%5D%2C%22D%22)%2F7)*2-IF(WEEKDAY(%5BEndDate%5D)%3CWEEKDAY%3E%3C%2FWEEKDAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22WorkingDays.png%22%20style%3D%22width%3A%20555px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351724i08CC1C1523B264F4%2Fimage-dimensions%2F555x419%3Fv%3Dv2%22%20width%3D%22555%22%20height%3D%22419%22%20role%3D%22button%22%20title%3D%22WorkingDays.png%22%20alt%3D%22WorkingDays.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I'd%20have%20to%20give%20more%20thought%20to%20the%20ISBLANK%20aspect%20of%20this%2C%20unless%20someone%20else%20has%20any%20thoughts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi!

 

I'm new to the sharepoint platform and currently working on a project where it will count the number of days from "Start Date" to "End Date" excluding weekends and if the End date ISBLANK, it should calculate the date TODAY.

 

I hope someone can help and thanks in advance!

 

Cheers!

1 Reply
best response confirmed by ash052621 (Visitor)
Solution

@ash052621 to get you started the formula for the number of working days between the StartDate and EndDate columns is as follows:

=(DATEDIF([StartDate],[EndDate],"D"))-INT(DATEDIF([StartDate],[EndDate],"D")/7)*2-IF(WEEKDAY([EndDate])<WEEKDAY([StartDate]),2, IF(OR(WEEKDAY([EndDate])=7,WEEKDAY([StartDate])=1),1,0))+1

 

WorkingDays.png

 

But I'd have to give more thought to the ISBLANK aspect of this, unless someone else has any thoughts.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)