[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date Calculation

Posted on 2009-04-01
8
Medium Priority
?
357 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
7 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

872 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