Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Date Calculation

Posted on 2009-04-01
8
Medium Priority
?
356 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

618 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