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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
577
Fluff
Fluff
S
  • Solved
lookup return from range of dates
Replies
4
Views
512
Peter_SSs
Peter_SSs
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
V
  • Solved
Week Number
Replies
2
Views
346
vmjan02
V
B
  • Question Question
calculate monthly value
Replies
0
Views
280
BrutalLogiC
B

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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