DateDiff - date/time - Total hours worked from Evening to next Morning

[access_learner via AccessMonster.com]

Hi,

I am not able to get the correct total hours worked when the date/time passes
into next morning.

I am using this formula--> Hours_Worked: DateDiff("n",[Day_0_Start],
[Day_0_Stop])/60

This formula gives correct hours for Employee #2.
But for #1 it gives -3495.5. Employee 1 works from March 31 starting at 23:
00:00 and finishes the following morning at April 1 at 7:30:00.

Here is the sample output:

Employee DAY_0_START DAY_0_STOP
Hours_Worked
1 03/31/2010 23:00:00 04/01/2010 07:30:00
-3495.5
2 03/31/2010 13:30:00 03/31/2010 22:15:00
8.75

I have searched DateDiff on Access Monster as well as Google but nothing
seems to make sense that will explain how to do calculation when employee
works into next morning.

Can someone please provide detailed solution using Access 2003 that will
allow me to calculate the correct number of hours regardless of when the
employee starts or finishes their shift which includes working into next
morning.

Looking forward to early reply.

Thank you.

 

[Jeff Boyce]

It all starts with the data.

What data type is being stored in [Day_0_Start] and [Day_0_Stop]? Are they
true Date/Time values? Are they only dates?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[access_learner via AccessMonster.com]

Hi there,

In the table, both columns are stored as text data type.
What are my next steps to make this work since I have large amount of data.

Thanks

Access Learner

It all starts with the data.

What data type is being stored in [Day_0_Start] and [Day_0_Stop]? Are they
true Date/Time values? Are they only dates?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

Hi,

[quoted text clipped - 31 lines]

Thank you.

 

[Jeff Boyce]

So you're saying that the "dates" you have stored are actually "text"?

Access' built-in date/time-related functions don't work (or don't work well)
on text -- they're optimized for date/time values.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi there,

In the table, both columns are stored as text data type.
What are my next steps to make this work since I have large amount of
data.

Thanks

Access Learner

It all starts with the data.

What data type is being stored in [Day_0_Start] and [Day_0_Stop]? Are
they
true Date/Time values? Are they only dates?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

Hi,

[quoted text clipped - 31 lines]

Thank you.

 

[access_learner via AccessMonster.com]

Hi Jeff,

I have changed the data type in the table to Date/Time and formatted the
fields of both columns to mm/dd/yyyy hh:nn:ss

Here is the output of the table:

Employee Day_0_Start Day_0_Stop
1 03/31/2010 23:00:00 01/04/2010 7:30:00
2 03/31/2010 13:30:00 03/31/2010 22:15:00


Formula used:
Hours_Worked: DateDiff('n',[Day_0_Start],[Day_0_Stop])/60


Query Output:

Employee Day_0_Start Day_0_Stop Hours_Worked
1 31/03/2010 11:00:00 PM 04/01/2010 7:30:00 AM -2079.5
2 31/03/2010 1:30:00 PM 31/03/2010 10:15:00 PM 8.75


Employee #1 is still giving negative number -2079.50 where the correct answer
should be 8.5 hours.

Why am I still not getting the correct answer for employee working from 11:
00pm to 7:30am into next morning?

Thanks

Access Learner

So you're saying that the "dates" you have stored are actually "text"?

Access' built-in date/time-related functions don't work (or don't work well)
on text -- they're optimized for date/time values.

Regards

Jeff Boyce
Microsoft Access MVP

Hi there,

[quoted text clipped - 24 lines]

 

[PieterLinden via AccessMonster.com]

Well, your data in the table appears to be wrong.

Employee Day_0_Start Day_0_Stop
1 03/31/2010 23:00:00 01/04/2010 7:30:00
2 03/31/2010 13:30:00 03/31/2010 22:15:00

The problem is that the first record *should* return a negative number, and
it does. So it's not the formula that's wrong, it's the data that's screwed
up.

 

[access_learner via AccessMonster.com]

I was able to get the correct answer by fixing the formatting of both columns
to same as (dd/mm/yyyy hh:nn:ss) for data type date/time in the table.

Formula gave me the correct hours.

Thanks Pieter for pointing out the error.

Regards

Access Learner