## thread181-1435685 solution for s

## thread181-1435685 solution for s

(OP)

thread181-1435685: overnight time calclulation

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

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

## RE: thread181-1435685 solution for s

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

## RE: thread181-1435685 solution for s

## RE: thread181-1435685 solution for s

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

## RE: thread181-1435685 solution for s

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: thread181-1435685 solution for s

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

## RE: thread181-1435685 solution for s

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

## RE: thread181-1435685 solution for s

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.