Solved

Calculate End (Payroll Date) from Start Date

Posted on 2010-11-10
10
756 Views
Last Modified: 2013-12-18
I have a app that associates request time off in. Currently it display time by a pay period but I have been asked to change that to display the date they are paid.
The pay periods are every two weeks. Pay period starts on a Saturday and ends 14 days later on a Friday. Example, 11/6 to 11/19.  I was trying to find a way to calculate what the Payroll date will be for an entry based on the Start Date of the request.

I can find the week (example, week 22) in the code I have but I have no clue how to display the payroll date.  Any help would be great!
Thanks
0
Comment
Question by:kali958
[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
  • 6
  • 4
10 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34104855
For LotusScript, use NotesDateTime.AdjustDay...

      Dim endDate As New NotesDateTime(startDate)
      Call endDate.AdjustDay(14)

In Formula, use @Adjust...

      endDate := @Adjust(startDate; 0; 0; 14; 0; 0; 0);
0
 

Author Comment

by:kali958
ID: 34104878
I have used that before, but my concern was that if the payroll week is 14 days from 11/6 to 11/19, how do I have the dates between all display the end date of 11/19? I can take the first date and adjust that 14 but then how do I do the followings dates. Does that make sense?
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34105199
You can't.  You have to have the start date to compute anything.  You must have that stored somewhere, though.
0
Technology Partners: 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!

 

Author Comment

by:kali958
ID: 34105254
So it is not driven from the start date of the request. I would have to define the start date and end date of the payroll week(s) in a profile document for example?

I can not not have notes calc the payroll date from just the start date of the request?
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34105412
Let's back up a bit.  I need to know the context in which the value is needed.  In your question above, you said "calculate what the Payroll date will be for an entry based on the Start Date of the request".

By "entry" do you mean a NotesViewEntry?  Is the "entry" the same as the "request"?  Are these both NotesDocuments?  Basically, I need to know what you are trying to accomplish.  For example, I can't tell if you're computing a value for a form field, a column value, a view category, or something else.
0
 

Author Comment

by:kali958
ID: 34105508
Here is my example
I need to take a day off - 11/17. So then I fill out a request in our Attendance Database for that day off.

The manager I was working at would like to know what payroll week that entry falls into.  So, for us. Our payroll is bi-weekly. It starts on a Saturday and ends 14 days later on a Friday. For example, the payroll period for the day off 11/17 would be 11/6 to 11/19.  They would like a view that would categorize time off by payroll week.
Currently I take the start date of the request and display it with the week that it is in.

So a view that would be :
>11/19/10
          Kali 11/17/2010  8 Hrs Vacation
I was thinking that I could take the date of the request and adjust the date somehow to calculate the payroll end date.

Does that help?
0
 
LVL 22

Accepted Solution

by:
Bill-Hanson earned 500 total points
ID: 34106461
Yes, it does.

What you need to do is start with a baseline date - a date that you know was the start of a payroll week.  It can be any date as long as it's in the past.  Store that somewhere (or hard code it into a variable).

Now take the difference between the day off and the baseline date, divide by 86400 to get the number of days and take the modulo of that and 14 to get the number of days since the last payroll start date.

Next, adjust your day off by the number of days since the last payroll start date to get the first day in the payroll week.

Then, adjust the start date by 13 days to get the last day in the payroll week.

That's it.

I would recommend setting up two fields on your form:

Field 1: payrollWeekDisplay (Text - Computed)
Here's the formula.  We also set a field value for the start date (see hint below).

      baseDate := [11/06/2010];
      seconds := dayOff - baseDate;
      days:= @Modulo(seconds / 86400; 14);
      startDate := @Adjust(dayOff; 0; 0; -days; 0; 0; 0);
      endDate := @Adjust(startDate; 0; 0; 13; 0; 0; 0);
      FIELD payrollWeekStartDate := startDate;
      @Text(startDate) + " - " + @Text(endDate);

Field 2: payrollWeekStartDate (Date/Time - Computed When Composed)
The formula can be anything since the payrollWeekDisplay formula sets its value explicitly.  I normally just use the field name itself, so...

      payrollWeekStartDate
      
Now, just use the [payrollWeekDisplay] field for the category in your view!

HINT: In order to sort these categories in date order, add a hidden column before the category column sorted by payrollWeekStartDate.  If you don't need the start date, then you can remove the payrollWeekStartDate field and the line of code that references it in the payrollWeekDisplay formula.

WARNING! If the base date is too far in the past, (dayOff - baseDate) will overflow and cause errors.  The only way to avoid this is to re-write this as a loop (which I hate) or keep the base date value in a profile document which gets updated by an agent every so often.  I tested this using a day off 5 years in the future, so it should not be a problem anytime soon :).
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34106507
Oops, I missed a small point in the example above.

Add a line like this to the top of the formula...

      dayOff := NAME_OF_YOUR_FIELD;

So now it looks like...

      dayOff := NAME_OF_YOUR_FIELD;
      baseDate := [11/06/2010];
      seconds := dayOff - baseDate;
      days:= @Modulo(seconds / 86400; 14);
      startDate := @Adjust(dayOff; 0; 0; -days; 0; 0; 0);
      endDate := @Adjust(startDate; 0; 0; 13; 0; 0; 0);
      FIELD payrollWeekStartDate := startDate;
      @Text(startDate) + " - " + @Text(endDate);
0
 

Author Comment

by:kali958
ID: 34106645
Brilliant!!!!   That is what I was looking for. I was missing the piece of setting a beginning Payroll Date. I think I was thinking to hard about it or I was thinking I had to define each two weeks period in a profile document. I am fine with creating one base payroll date for the year.  Perfect~

Great answer Bill - thank you so much for your help and explaining this so I understood what the solution is rather then just copying something and not understanding it.
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 34106691
Happy to help!
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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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