EXCEL - from today's date I want to find the next Friday (Every second Friday from a given date)

%3CLINGO-SUB%20id%3D%22lingo-sub-1321511%22%20slang%3D%22en-US%22%3EEXCEL%20-%20from%20today's%20date%20I%20want%20to%20find%20the%20next%20Friday%20(Every%20second%20Friday%20from%20a%20given%20date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321511%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20hoping%20to%20achieve%20this%20all%20in%20one%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20crews%20have%20every%20second%20Friday%20off%20work%2C%20so%20if%20the%20cell%20can%20calculate%20what%20todays%20date%20is%20then%20given%20a%20specific%20start%20date%20it%20will%20identify%20the%20next%20Friday%20on%20a%20two%20week%20rotation.%3C%2FP%3E%3CP%3Eeg%3C%2FP%3E%3CP%3Eif%20the%20start%20date%20I%20enter%20is%2017%2F04%2F20%3C%2FP%3E%3CP%3Ethen%20on%20the%20cell%20it%20will%20show%20a%20date%20of%2001%2F05%2F20%2C%20once%20the%26nbsp%3B01%2F05%2F20%20passes%20I%20would%20like%20the%20same%20cell%20to%20show%2015%2F5%2F20%2C%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20know%20I%20can%20enter%20a%20date%20then%20%2B14%20days%20to%20get%20the%20next%20date%20but%20this%20would%20require%20I%20whole%20column%20and%20it%20does%20not%20remove%20the%20previous%20dates%2C%20I%20really%20only%20want%20to%20see%20the%20one%20date%20which%20will%20be%20the%20next%20day%20off.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20perhaps%20create%20the%20list%20off%20the%20page%20where%20it%20cannot%20be%20displayed%20but%20still%20not%20sure%20how%20to%20get%20only%20the%20most%20relevant%20date%20to%20be%20seen.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1321511%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324477%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20-%20from%20today's%20date%20I%20want%20to%20find%20the%20next%20Friday%20(Every%20second%20Friday%20from%20a%20given%20date)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629207%22%20target%3D%22_blank%22%3E%40treloar86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20A1%20is%20the%20start%20date%2C%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DA1%2B(INT((TODAY()-A1)%2F14)%2B1)*14%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFor%20example%2C%20if%20in%20A1%20is%20Jan%2010%2C%202020%20and%20today%20is%20Apr%2021%2C%20formula%20returns%20May%2001%2C%202020%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I'm hoping to achieve this all in one cell.

 

My crews have every second Friday off work, so if the cell can calculate what todays date is then given a specific start date it will identify the next Friday on a two week rotation.

eg

if the start date I enter is 17/04/20

then on the cell it will show a date of 01/05/20, once the 01/05/20 passes I would like the same cell to show 15/5/20, and so on.

 

I hope this makes sense.

 

I do know I can enter a date then +14 days to get the next date but this would require I whole column and it does not remove the previous dates, I really only want to see the one date which will be the next day off.

 

I could perhaps create the list off the page where it cannot be displayed but still not sure how to get only the most relevant date to be seen.

 

thank you in advance.

1 Reply
Highlighted

@treloar86 

If in A1 is the start date, the formula could be

=A1+(INT((TODAY()-A1)/14)+1)*14

For example, if in A1 is Jan 10, 2020 and today is Apr 21, formula returns May 01, 2020