Microsoft Office Forums Excel Date Calculation

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: 478
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
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, 6 views)
Reply With Quote
  #3  
Old 11-18-2017, 01:15 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel Date Calculation Windows 7 64bit Excel Date Calculation Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,397
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

If you have a post 2007 version look at the NETWORKDAYS.INTL function
If not their are tons of solutions out there
__________________
Please note I am not active on this forum anymore
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 Date field - future date calculation + only business days neon4 Word 7 01-21-2016 02:21 PM
Excel Date Calculation Age calculation with date and DOB fields Xaand Word 7 02-21-2014 09:15 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


All times are GMT -7. The time now is 12:43 PM.


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