INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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

How to extract Hours and Minutes in one field using DartPart

How to extract Hours and Minutes in one field using DartPart

(OP)
I'm trying to find the difference between two dates in minutes. Unfortunately there is an issue with using the DateDiff function. One date is a login date which would be a current date and time. The second date is a system defined start of shift. Here is the problem, when a shift is created you enter the start time and end time of the shift i.e. 7am to 3:30pm. The system assigns a date to each entry of 12/31/1899 resulting in 12/31/1899 07:00:00. Which makes it unusable in the DateDiff function. I thought that perhaps I could extract the Hours and Minutes from each start time and then compare the result but I haven't been able to use one formula to extract the hours and minutes. Any ideas on this issue?

RE: How to extract Hours and Minutes in one field using DartPart

You can use the TimeValue() function to remove any date portion of a datetime value.

CODE --> Debug

? Now()
12/23/2014 1:40:59 PM 
? TimeValue(now())
1:41:07 PM 

Duane
Hook'D on Access
MS Access MVP

RE: How to extract Hours and Minutes in one field using DartPart

Hi,

What a mess!

Your system does not assign a date of 12/31/1899. That's just a ZERO date.

Your system DESIGNER failed to assign a date at all to the start and end times! He or she ought to be hanged by the thumbs until the EndDateEndTime expires!

Then ASSUMING that the start and end occur in the same day, simply subtract the start from the end which results in a fractional number of days. Then use the Format() function to return the minutes and seconds as a string.

Format(EndTime-StartTime,"nn:ss")

RE: How to extract Hours and Minutes in one field using DartPart

(OP)

Skip,
I'm not sure if you understood what I am trying to do or perhaps I don't understand you're post. I'm trying to find the difference between the actual login and the start of shift to determine how late a given login is. I will use the same logic to determine if a given logout punch is prior to the end of shift. Hope this helps clarify what I'm trying to achieve.

RE: How to extract Hours and Minutes in one field using DartPart

BradCustom,
Did you attempt to implement my suggestion? You should be able to use something like:

CODE --> Expression

DateDiff("n",TimeValue([Login Date]),[Shift Start]) 


The result will be positive or negative depending on the chronological order.

Duane
Hook'D on Access
MS Access MVP

RE: How to extract Hours and Minutes in one field using DartPart

(OP)
dhookon, I just tried your solution and it gives me a result in seconds which is something I can work with so thanks very much for your help!! Brad

RE: How to extract Hours and Minutes in one field using DartPart

I hope over last seven years you found some of the TT posts helpful. If so, please use/click on

Like this post?
Star it!

to show appreciation for help received.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: How to extract Hours and Minutes in one field using DartPart

BradCustom,
The result should be in minutes, not seconds. You might want to validate your results. The "n" is for Minutes ("m" is Months).

Duane
Hook'D on Access
MS Access MVP

RE: How to extract Hours and Minutes in one field using DartPart

(OP)
dhookom, yes I know that "n" stands for minutes. From your earlier post I thought the result would be an actual time i.e. 1:41:07 pm. I guess, I miss read your post. Actually, the result I wanted was in minutes. Thanks, Brad

RE: How to extract Hours and Minutes in one field using DartPart

"Actual time"

Then use "d" for DAYS in your DateDiff() and Format() as "h:nn:ss am/pm"

RE: How to extract Hours and Minutes in one field using DartPart

Might be better to take your minutes result, divide by 24*60 and them Format()

RE: How to extract Hours and Minutes in one field using DartPart

I don't think DateDiff("d",....) will work since it returns the integer number of day boundaries between two date expressions.

My previous suggestions return minutes and should not be formatted as DateTime.

Duane
Hook'D on Access
MS Access MVP

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!

Resources

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