SOLVED

Develop my formula dates

%3CLINGO-SUB%20id%3D%22lingo-sub-673302%22%20slang%3D%22en-US%22%3EDevelop%20my%20formula%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673302%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20attached%20excel%20sheet%20with%20my%20next%20order%20date%2C%20date%20frequency%20and%20next%20delivery%20dates%20also%20in%20there.%20i%20need%20to%20amend%20formula%20if%20my%20next%20delivery%20date%20on%20Saturday%20%2C%20Should%20change%20it%20to%20last%20Friday%20(deduct%20a%20date)%20and%20if%20my%20next%20delivery%20date%20on%20Sunday%20%2C%20need%20to%20change%20it%20to%20next%20Monday%20(Increase%20a%20date)%20.%20Simply%20i%20need%20to%20deduct%20if%20my%20date%20shows%20Saturday%20and%20need%20to%20increase%20if%20shows%20it%20as%20Sunday%26nbsp%3B%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-673302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673333%22%20slang%3D%22en-US%22%3ERe%3A%20Develop%20my%20formula%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673333%22%20slang%3D%22en-US%22%3EThe%20formula%20you%20need%20in%20G2%2C%20copied%20down%20rows%20and%20across%20columns%2C%20is%3A%3CBR%20%2F%3E%3D%24E2%2B%24D2-%3CBR%20%2F%3E(WEEKDAY(%24E2%2B%24D2)%3D7)%2B%3CBR%20%2F%3E(WEEKDAY(%24E2%2B%24D2)%3D1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673436%22%20slang%3D%22en-US%22%3ERe%3A%20Develop%20my%20formula%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you.%20you%20are%20amazing%20man%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-673530%22%20slang%3D%22en-US%22%3ERe%3A%20Develop%20my%20formula%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673530%22%20slang%3D%22en-US%22%3EIt's%20my%20pleasure%20to%20help%20you!%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Here attached excel sheet with my next order date, date frequency and next delivery dates also in there. i need to amend formula if my next delivery date on Saturday , Should change it to last Friday (deduct a date) and if my next delivery date on Sunday , need to change it to next Monday (Increase a date) . Simply i need to deduct if my date shows Saturday and need to increase if shows it as Sunday  .

3 Replies
Highlighted
Solution
The formula you need in G2, copied down rows and across columns, is:
=$E2+$D2-
(WEEKDAY($E2+$D2)=7)+
(WEEKDAY($E2+$D2)=1)
Highlighted

@Twifoo 

 

thank you. you are amazing man 

Highlighted
It's my pleasure to help you!