How to extract Hours and Minutes in one field using DartPart 1

[BradCustom]

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?

 

[dhookom]

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

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

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

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")

 

[BradCustom]

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.

 

[dhookom]

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

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

 

[BradCustom]

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

 

[Andrzejek]

I hope over last seven years you found some of the TT posts helpful. If so, please use/click on
[blue]
Like this post?
Star it![/blue]
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.

 

[dhookom]

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

 

[BradCustom]

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

 

[SkipVought]

Actual time"

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

 

[SkipVought]

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

 

[dhookom]

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