Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here


thread181-1435685 solution for s

thread181-1435685 solution for s

thread181-1435685 solution for s

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

RE: thread181-1435685 solution for s

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.

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

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.


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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: thread181-1435685 solution for s

What I needed is


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:

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.

Hook'D on Access
MS Access MVP 2001-2016

RE: thread181-1435685 solution for s

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close