?
Solved

Date Calculation

Posted on 2009-04-01
8
Medium Priority
?
354 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 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

764 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