How to replace weekend and capture the next business day

[kumar1803]

Hello,

I have a record with dates that included the weekends in there.

I would like to find and replace the weekend days to next business day.

1-Jun-2014

1-Feb-2015

1-Jun-2014

1-Nov-2014

1-Mar-2015

1-Nov-2015

1-Nov-2015

1-Nov-2015

1-Nov-2015

1-Oct-2016

1-Oct-2016

1-Oct-2016

1-Oct-2016

1-May-2016

1-Oct-2017

18-Nov-2018

31-Mar-2019

31-Mar-2019

31-Mar-2019

31-Mar-2019

30-Jun-2019

15-Jan-2017

15-Jan-2017

15-May-2016

15-Dec-2018

15-Sep-2018

15-Dec-2018

15-Jun-2019

15-Jun-2019

1-Mar-2014

1-Feb-2015

1-May-2016

1-Oct-2016

1-Jul-2017

1-Apr-2018

1-Apr-2018

1-Sep-2018

1-Apr-2018

1-Jul-2018

1-Sep-2018

1-Sep-2018

1-Sep-2018

1-Jun-2019

1-Jun-2019

1-Jun-2019

<colgroup><col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;"> <tbody> </tbody>

 

[Joe4]

For a date in cell A1, try this formula:

=IF(WEEKDAY(A1,11)<6,A1,WORKDAY(A1-1,1))

If the day in cell A1 is a weekday, it will return that day. Otherwise, it will return the following Monday.

 

[kumar1803]

Thank you!

The formula works great. However my date format is like this:

20160407
20161017
20151217
20161201
20170418
20180122
20170418
20171208

<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody> </tbody>

 

[Joe4]

That looks significantly different from your original post.

It does not matter what your date format is, as long as the entries are actually dates, and not text or numbers.
Can you confirm? An easy way to check is to right-click on any cell, select "Format Cells...", go to the Number tab and see which number format is being applied.

If you see those values that you posted and your format is "General" or "Text", you do not have valid date entries.
If they are not entered as actual dates, you will need to convert them to dates. One way is to use Text to Columns.