Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1.1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 326 Dynamics CRM
- 110 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 994 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
DateTime - Quarter Hour Rounding Rule for Payroll
Steve
Member Posts: 81
Hello All,
I'm completing a payroll system within Navision and the data received is in DateTime fields and requires rounding (using the 15/7 quarter hour rule). I have figured a solution for this whereas I bring to the minutes to int and evaluate them against a table and then decide if the round is up or down and then try to evaluate this back to time and the back to date time.
This method is very clunky with several areas that could potentially fail especially on a 11:59pm (23:59) punch that would require change both date and time to the next day.
The rules are as follows.
If the minute are
00-07 round to 00
08-15 round to 15
16-22 round to 15
23-30 round to 30
31-37 round to 30
38-45 round to 45
46-52 round to 45
53-59 round to 60 ---> or the next day if 11:59PM (23:59)
Does anyone have ideas to streamline this process?
Thanks
I'm completing a payroll system within Navision and the data received is in DateTime fields and requires rounding (using the 15/7 quarter hour rule). I have figured a solution for this whereas I bring to the minutes to int and evaluate them against a table and then decide if the round is up or down and then try to evaluate this back to time and the back to date time.
This method is very clunky with several areas that could potentially fail especially on a 11:59pm (23:59) punch that would require change both date and time to the next day.
The rules are as follows.
If the minute are
00-07 round to 00
08-15 round to 15
16-22 round to 15
23-30 round to 30
31-37 round to 30
38-45 round to 45
46-52 round to 45
53-59 round to 60 ---> or the next day if 11:59PM (23:59)
Does anyone have ideas to streamline this process?
Thanks
Steve
0
Answers
From there you can determine the number of seconds / minutes / hours using the FORMAT function. Then a large CASE statement should do.
May be an easier way, but it's early on a Saturday morning and I'm still a little tired.
That is very easy
ROUNDDATETIME(DateTime,15 * 60000)
Best Regards
Bjarne
I have never seen that function. Must take a look and see what it does.
Neat function, I never saw it before, thanks for pointing it out.