How to replace weekend and capture the next business day

K

kumar1803

Board Regular
Joined
Jan 7, 2015
Messages
110
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.

[TABLE="width: 112"]
<colgroup><col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;"> <tbody>[TR]
[TD="width: 149, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jun-2014[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Feb-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jun-2014[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Nov-2014[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Mar-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Nov-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Nov-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Nov-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Nov-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-May-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2017[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]18-Nov-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]31-Mar-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]31-Mar-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]31-Mar-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]31-Mar-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]30-Jun-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Jan-2017[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Jan-2017[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-May-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Dec-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Sep-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Dec-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Jun-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]15-Jun-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Mar-2014[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Feb-2015[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-May-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Oct-2016[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jul-2017[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Apr-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Apr-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Sep-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Apr-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jul-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Sep-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Sep-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Sep-2018[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jun-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jun-2019[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C65911]#C65911[/URL] , align: right"]1-Jun-2019[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
For a date in cell A1, try this formula:
Code: 
=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.
 
Upvote 0
Thank you!

The formula works great. However my date format is like this:
[TABLE="width: 69"]
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 92, bgcolor: transparent"]20160407[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20161017[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20151217[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20161201[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20170418[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20180122[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20170418[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20171208[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Usign the =sort function in a Dynamic Array to sort by Month
Replies
3
Views
526
Fluff
Fluff
S
  • Solved
lookup return from range of dates
Replies
4
Views
496
Peter_SSs
Peter_SSs
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist
V
  • Solved
Week Number
Replies
2
Views
331
vmjan02
V
B
  • Question Question
calculate monthly value
Replies
0
Views
219
BrutalLogiC
B

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top