Solved

Date Calculation

Posted on 2009-04-01
8
346 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
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 80

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 80

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now