×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

#### Jobs

(OP)

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

Hi SmilyLex,

Welcome to Tek-tips.

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

### RE: thread181-1435685 solution for s

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.

### RE: thread181-1435685 solution for s

(OP)
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

### RE: thread181-1435685 solution for s

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,

Just traded in my OLD subtlety...
for a NUance!

### RE: thread181-1435685 solution for s

(OP)
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

### RE: thread181-1435685 solution for s

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

### RE: thread181-1435685 solution for s

(OP)
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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!