Paydate column using Builder

[GMLee]

Hello


I'm trying to use Builder to create a column in my table that lists the Paydate based of the week.


Week of 09/17/2017 is paydate 10/06/2017
Week of 09/24/2017 is paydate 10/06/2017
Week of 10/1/2017 is paydate 10/20/2017
Week of 10/8/2017 is paydate 10/20/2017


The formula below is what I am using in Builder but I am getting dates that are 2 days past what is correct.


[WEEK_OF]+IIf([WEEK_OF]-(Round([WEEK_OF]/14,0))*14=-6,8,1)+20


Thak you for reading!

 

[xenou]

Is WEEK_OF a date?

I'm not sure what you are expecting by dividing a date by 14 here:
Round([WEEK_OF]/14,0)

Personally I think simplest is to just have a table of ending dates and pay dates - especially since that is useful information in many reports, forms and queries and you will probably find it easier to refer to a table rather than use a complicated function every time you need to work with paydates.

 

[GMLee]

Thanks for the reply.

I've tried using a table but the date column is an interval so unless I populate the table with ever 15 minutes interval and corresponding paydate it doesn't work.

Week_of is a date, it returns the Sunday of the interval. I am very new to using Builder and Access so I started with the Excel formula I use -
=[@[Week of]]-MOD([@[Week of]],14)+IF(MOD([@[Week of]],14)<10,20,34)

Which I believe I have simplified, but don't' have the new formula handy.

 

[xenou]

Will running a function in the Access table be slowing than using a table?

Not sure. Depends on the size of tables and complexity of functions, and how often you are invoking your function or using your table. As a rule, I don't think you would find performance an issue with a table since it will be (by definition) a pretty small table if you are only including calendar data keyed to calendar dates, with a clear primary key strategy that will enhance lookups.

I had thought that using the builder would be easier than keeping a table populated with paydates.

I wouldn't say it's very hard. On the other hand, you do need to have a function that works. My biggest concern would be how to make sure this function is used consistently and how to prove that it is correct -- I'd still probably test it with a table of paydates to make sure that the function provides the correct output. One of the huge flaws of software development is a lack of rigorous testing.

 

[GMLee]

Thanks you. I had updated my initial reply after going back and adding a table.


I've tried using a table but the date column is an interval so unless I populate the table with ever 15 minutes interval and corresponding paydate it doesn't work.


Week_of is a date, it returns the Sunday of the interval. I am very new to using Builder and Access so I started with the Excel formula I use -
=[@[Week of]]-MOD([@[Week of]],14)+IF(MOD([@[Week of]],14)<10,20,34)


Which I believe I have simplified, but don't' have the new formula handy.

 

[xenou]

I don't think you formula works, even in Excel. I tried it with input of 10/19 and it returns 11/3 which is a Friday. I don't understand the formula -- which is why I am asking about it (to me, it neither should work nor does work).

Why would paydates be sensitive to 15 minute time values? Every payroll accounting I have ever seen bases pay on days worked, not 15 minute intervals worked.

 

[GMLee]

My apologies for not being more clear.

The paydate for activity on 10/19 is 11/3 so that is correct. The date I am rolling up is call volumes. So I am looking for the ability to sum the total number of calls in each payperiod.

Queue Interval Offered
Call Volume 9/1/2017 3:45:00 PM 1
Call Volume 10/14/2017 1:15:00 PM 2
Call Volume 10/14/2017 3:30:00 PM 1
Call Volume 10/16/2017 10:30:00 AM 1
Call Volume 10/16/2017 11:30:00 AM 1
Call Volume 10/16/2017 1:00:00 PM 1

 

[xenou]

Sorry, just don't understand. Why are there three payperiods on 10/16?

Call Volume 10/16/2017 10:30:00 AM 1
Call Volume 10/16/2017 11:30:00 AM 1
Call Volume 10/16/2017 1:00:00 PM 1

I still don't really know what you want the formula to do.

 

[GMLee]

Queue	Interval	Calls	Paydate
Queue1	10/14/2017 3:45:00 PM	20	10/20/2017
Queue1	10/16/2017 1:15:00 PM	2	11/3/2017
Queue1	10/16/2017 3:30:00 PM	5	11/3/2017
Queue2	10/14/2017 3:45:00 PM	15	10/20/2017
Queue2	10/16/2017 11:30:00 AM	30	11/3/2017
Queue2	10/20/2017 1:00:00 PM	6	11/3/2017

<tbody>
</tbody>

I have data in a table by 15 minute interval. I need to add the paydate column to be able to summerize the calls by paydate.

Total 10/20/2017 Calls - 35
Total 11/3/2017 Calls - 43

I hope this help clear up what I am asking for.

 

[xenou]

I would create a table with dates, paydates. That's what I said at the start. Then you can do a simple lookup. Otherwise, I guess if you don't want to go that route you can create a function but it needs to be a function that works which apparently you don't have. How do you define your paydates? (Edit: note that this last question is I think probably necessary to be answered because otherwise not really sure how anyone would write or test a function without knowing what the result is supposed to be - but as I said I think a table-based approach is simple and good).