Solved

Date Calculation

Posted on 2009-04-01
8
353 Views
Last Modified: 2013-12-18
I need a excel formula or a VBA to solve this...

I pay my supplier invoice on the 14th day from the date of invoice (including weekends and holidays). However for him to be paid on the 14th day, i need to kick start a process 4 working days earlier than the due date. If the 14th day is a holiday then i would need to pay him on the 13th day and i would still need to have a gap of 4 working days prior.

Can you please help ?
0
Comment
Question by:dvivek_aca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24040226
Hi,
Can you please upload your sample file, Along with what the holidays that you consider? Also with the result that you are looking for..
Saurabh...
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 24040332
You can use the NETWORKDAYS and WORKDAY functions to return your payment date and process start dates.

If cell A1 contains the invoice date, then the payment date (taking holidays into account) is:
=WORKDAY(A1,NETWORKDAYS(A1,A1+14,Holidays)-1,Holidays)

The process start date (four business working days earlier) is given by:
=WORKDAY(A1,NETWORKDAYS(A1,A1+14,Holidays)-5,Holidays)

Holidays is a named range that contains a list of all your business holidays. You could alternatively use a fixed address range like $D$2:$D$13 instead of creating the named range Holidays.

If you see #NAME? as the result, then you need to enable the Analysis ToolPak add-in using the Tools...Add-Ins menu item. Just check the box for Analysis ToolPak (and ignore the checkbox for Analysis ToolPak - VBA). You won't have this problem in Excel 2007 because the Analysis ToolPak functions are enabled by default.

Brad
0
 
LVL 7

Author Comment

by:dvivek_aca
ID: 24040377
Here we go with sample data
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 24040720
You will need a record stored somewhere that defines your working days and your holidays.  I store mine in a central configuration database in Lotus Notes.  The form has a field for workdays ("Mon|2":"Tue|3":"Wed|4":"Thu|5":"Fri|6":"Sat|7":"Sun|1") and a multi-value date field for entering holidays.

Once you have this setup, your VBA code can get the document and use the information to calculate the date you need.

I've provided some sample code below that illustrates the concept.
Sub Test()
  
  Dim sess As Object, db As Object, calendar As Object
  Dim workdays As Variant, holidays As Variant
  
  ' init session
  Set sess = CreateObject("Lotus.NotesSession")
  sess.Initialize ("")
  
  ' get workdays and holidays
  Set db = sess.GetDatabase("Server/Domain", "applications\config.nsf", False)
  Set calendar = db.GetDocumentById("27AE")
  workdays = calendar.GetItemValue("WorkDays")
  holidays = calendar.GetItemValue("Holidays")
  
  ' start with the invoice date
  Dim targetDate As Date
  targetDate = DateSerial(2009, 5, 1)
  
  ' add 14 days
  targetDate = DateAdd("d", 14, targetDate)
  
  ' check workdays
  Do While (Not ArrayIsMember(workdays, CStr(Weekday(targetDate))))
    targetDate = DateAdd("d", -1, targetDate)
  Loop
  
  ' check holidays
  Do While (ArrayIsMember(holidays, CStr(targetDate)))
    targetDate = DateAdd("d", -1, targetDate)
  Loop
  
  ' now, just subtract the 4 days
  targetDate = DateAdd("d", -4, targetDate)
  
  MsgBox CStr(targetDate)
  
End Sub
 
Function ArrayIsMember(source As Variant, value As String) As Boolean
  ArrayIsMember = True
  Dim entry As Variant
  For Each entry In source
    If (entry = value) Then Exit Function
  Next
  ArrayIsMember = False
End Function

Open in new window

0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 24041152
I'm not sure it needs to be as complicated as Brad's suggestion. For process start date try the following:
=WORKDAY(A1+15,-5,holidays)
where A1 contains invoice date and holidays a list of holiday dates
regards, barry
0
 
LVL 7

Author Closing Comment

by:dvivek_aca
ID: 31565336
Thanks for both the solutions. I however had to do a macro to determine the due date that was neither a weekend or a holiday. From thereon the formulae helped me to get the date to initiate the payment.

0
 
LVL 81

Expert Comment

by:byundt
ID: 24049834
Using barryhoudini's approach, the due date is given by:
=WORKDAY(A1+15,-1,holidays)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question