Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-17-2017, 07:51 AM
artlk artlk is offline Excel Date Calculation Windows 7 64bit Excel Date Calculation Office 2007
Novice
Excel Date Calculation
 
Join Date: Nov 2017
Posts: 2
artlk is on a distinguished road
Default Excel Date Calculation

Hi,

I want to add 15 days to date. After adding 15 days to a date, if the date is under (holiday list, Saturday & Sunday) then final date should be next working day.

I tried this "=WORKDAY(A2,15,B$3:B$6)",

A2= starting date
15 =number of days to add


B$3:B$6= holiday list.

but this omits the Saturday's & Sunday's from the begning itself. what i need is Saturday and Sunday need to included., if in case the final date comes under Saturday/Sunday/Holiday then the result should be next working day

Thanks in advance.
Reply With Quote
  #2  
Old 11-17-2017, 11:07 AM
ArviLaanemets ArviLaanemets is offline Excel Date Calculation Windows 8 Excel Date Calculation Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

In attached file is a possible solution.

NB! It's better to leave the Weekday() function with European format - much easier for calculations when weekends are days 6 and 7.

PS. Without additional sheet it will be nearly impossible. You must move the date away from weekend, then move away from holiday when date happens to be one, then again you have to check that new dates aren't weekends and when , then move the date, then you have anew to check for holidays, etc.
Attached Files
File Type: xlsx FutureWorkday.xlsx (31.8 KB, 16 views)
Reply With Quote
  #3  
Old 11-18-2017, 01:15 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Excel Date Calculation Windows 7 64bit Excel Date Calculation Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If you have a post 2007 version look at the NETWORKDAYS.INTL function
If not their are tons of solutions out there
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 11-20-2017, 09:55 AM
artlk artlk is offline Excel Date Calculation Windows 7 64bit Excel Date Calculation Office 2007
Novice
Excel Date Calculation
 
Join Date: Nov 2017
Posts: 2
artlk is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
In attached file is a possible solution.

NB! It's better to leave the Weekday() function with European format - much easier for calculations when weekends are days 6 and 7.

PS. Without additional sheet it will be nearly impossible. You must move the date away from weekend, then move away from holiday when date happens to be one, then again you have to check that new dates aren't weekends and when , then move the date, then you have anew to check for holidays, etc.
Hi ArviLaanemets,

Thanks for your reply....
It's working fine
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Date Calculation Age calculation with date and DOB fields Xaand Word 9 09-29-2023 11:38 AM
Excel Date Calculation Date field - future date calculation + only business days neon4 Word 7 01-21-2016 02:21 PM
Excel Date Calculation Rules based due date calculation dlowrey Excel Programming 3 05-12-2013 08:30 PM
Excel Date Calculation Date Calculation Lights Excel 5 04-18-2012 04:31 AM
calculation of start date ketanco Project 1 02-29-2012 07:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft