thread181-1435685 solution for s

[SmilyLex]

thread181-1435685

solution for simultaneously dealing same day as well as overnight start and end time within 24hrs of each other (only One problem)

HrsWrked: IIf((DateDiff("n",[Start1],[End1],1,1)/60<0),(DateDiff("h",[Start1],[End1]+1) Mod 24),DateDiff("n",[Start1],[End1],1,1)/60)



The mod 24 portion (DateDiff("h",[Start1],[End1]+1) Mod 24) only returns an integer for example

19:00 (start1) and 4:15 (end1) produces 9 hrs not 9.25

PS I followed instructions on how to add to an old "closed" thread Sorry

 

[dhookom]

Hi SmilyLex,

Welcome to Tek-tips.

If you have something to add to a thread, you should reply in the thread rather than create a new one.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

This is a bad design. If you are doing time calculations, your datetime field should include the date portion as well, and these fields should not be seperate.

 

[SmilyLex]

Thanks Maj

The final result I need is Either (same day time gap X $rate) as well as (Overnight time gap x $rate).

Thus I need an decimal result so I can run an Iif query to sort which of 23+ category base formulas I need to apply to calculate wages.

Cheesrs
Alex

 

[SkipVought]

Ditto. Date and Time ought NOT to be disjoined. Rather Date/Time is the preferred storage method.

If you foolishly use Time only, you need to perform handstands and cartwheels in your code, in order to arive at a solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SmilyLex]

What I needed is

BasePay:
IIf((DateDiff("n",[Start1],[End1],1,1)/60<0),
((DateDiff("n",[Start1],[End1]+1)/60)*[Employee]![BaseRate]),
(DateDiff("n",[Start1],[End1],1,1)/60)*[Employee]![BaseRate])

It gives me
regular day time gap x $pay Rate or
overnight Time gap x $pay rate

remove the Pay rate and you get day or midnight time worked as a decimal

 

[dhookom]

I think your IIf() condition could simply be:
IIf([Start1]>[End1],

Can you provide a final answer regarding your requirements? How about some sample records with the desired output?

I report out of 33 corporate databases that store a single date and start and end times so I don't think its all that uncommon and can definitely be something we can't change.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SmilyLex]

I'm happy with my final solution. Many thanks for all the suggestions.


Any overnight (mid night) hours worked are more complicated to pay penalties for so identifying which shift go over midnight is the start of the calculation and may end up with far too many rates or portions of.
Regular hrs, < 8hrs 6:00 <> 19:00
Plus > 8hrs regular + OT
pre 19:00, (the portion prior to 19:00)
19:00 to 24:00, following a regular hrs portion in the same shift
24:00 to 6:00 portion, following a 19:00 to 24:00 in the same shift
post 6:00 in the same shift following overnight
Possibly of Split shift with portions over midnight
Which portion was not Saturday
Which portion was Saturday
Which portion was Sunday.
Which portion was at higher duties.
When breaks were taken if post 6 hrs straight or between 5-6 hrs of full shift,
And last but not least over time. for any of the above.