473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to compare 2 dates and times to get duration

116 100+
I am creating a time sheet and want to compare 2 dates and times to find the length of time taken.

1. I have a start date in one field and start time in another. I then want to enter a finish date in another field and finish time in another. I basically want to compare the fields to find the duration in time format. eg 6 hrs and 30 mins = 06:30

Thanks
Christine.
May 28 '10 #1

✓ answered by Jim Doherty

@jacc14
The following function will return the value you require and return nothing if a field is empty. Paste it into a new module and save then you can simply call the function in the query grid with a convention such as this Simply pass your field names into the function

TheField: HoursMins([DateFrom],[TimeFrom],[DateTo],[TimeTo])


Expand|Select|Wrap|Line Numbers
  1. Function HoursMins(df, tf, dt, tt)
  2. On Error Resume Next
  3.     If IsNull(df) Or IsNull(tf) Or IsNull(dt) Or IsNull(tt) Then
  4.         HoursMins = ""
  5.     Else
  6.         prd = Nz(DateDiff("n", df & " " & tf, dt & " " & tt))
  7.         If prd = 0 Then
  8.            HoursMins = "00:00"
  9.         Else
  10.            HoursMins = Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")
  11.         End If
  12.     End If
  13. End Function

15 4354
Jim Doherty
897 Expert 512MB
@jacc14
The following function will return the value you require and return nothing if a field is empty. Paste it into a new module and save then you can simply call the function in the query grid with a convention such as this Simply pass your field names into the function

TheField: HoursMins([DateFrom],[TimeFrom],[DateTo],[TimeTo])


Expand|Select|Wrap|Line Numbers
  1. Function HoursMins(df, tf, dt, tt)
  2. On Error Resume Next
  3.     If IsNull(df) Or IsNull(tf) Or IsNull(dt) Or IsNull(tt) Then
  4.         HoursMins = ""
  5.     Else
  6.         prd = Nz(DateDiff("n", df & " " & tf, dt & " " & tt))
  7.         If prd = 0 Then
  8.            HoursMins = "00:00"
  9.         Else
  10.            HoursMins = Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")
  11.         End If
  12.     End If
  13. End Function
May 28 '10 #2
NeoPa
32,556 Expert Mod 16PB
If none of the controls is blank then add [StartDate] to [StartTime, add [EndDate] to [EndTime], then subtract the first result from the second. This can be formatted as "HH:nn" if you choose.
May 28 '10 #3
jacc14
116 100+
thank you . This has worked perfect.

However it has give me the idea that i should estimate prior how long i expect the job to take.

I have a magazine that prints 2000 copies an hour. Therefore if i have a start date of 28/05/10 and start time of 18:30 and wish to produce 3000 copies I want to return 28/05/10 in the finish date and 20:00 in the finish time.

Many thanks
Christine
May 28 '10 #4
OldBirdman
675 512MB
I think that you should first combine the date & time fields into single fields. A date field stores both a date and a time. Having 2 fields, one for date and one for time will cause problems when the elapsed time crosses a date boundry (midnight).
NeoPa's suggestion in Post #3 avoids this by building the single field when needed. You could do this again here, but this issue may come up again as the project gains complexity.
Once you have the start date-time as a single value, then the DateAdd() function will compute the end date-time.
May 28 '10 #5
NeoPa
32,556 Expert Mod 16PB
I certainly support OB in that. It is a better idea where at all possible.

If the run is always 3,000 copies then you could use a specific value of 1.5 hours. Otherwise, if you have a [PrintRun] value, then the value to add would be :
Expand|Select|Wrap|Line Numbers
  1. [PrintRun] / 48000
48,000 relates to 2,000 copies per hour times 24 hours per day. A DateTime value reflects days as whole numbers and partials thereof as a fraction.
Jun 1 '10 #6
jacc14
116 100+
@Jim Doherty
Hi Jim. I wondered if you could assist. This function has worked a treat but i have just found that when it exceeds 24 hours it will return 00:00. Could you tell me what i need to do.
Many thanks
Chris
Jun 8 '10 #7
Jim Doherty
897 Expert 512MB
@jacc14
I have not had a problem with the function personally it returning hours and minutes calculated over 24 hours, beyond midnight boundaries and so on. I have attached a small db for you to compare implementation. Look at the query and table formats inside it to see how it compares with yours.
Attached Files
File Type: zip jacc14.zip (17.2 KB, 103 views)
Jun 8 '10 #8
jacc14
116 100+
Hi Jim
I now realise what i have done . I needed to sum all the times but unless i put in the function AS Date it treated as text. This worked fine for under 24 hrs. However I now realise that i cant put in AS DATE but it does give me a new problem. Is there something i can do to get round this please so i can sum all the times even if over 24 hours.
Thanks
Christine.
Jun 8 '10 #9
NeoPa
32,556 Expert Mod 16PB
Christine, you seem to have asked pretty well the same question as you did in post #7, but haven't responded in any way to Jim's post #8. It can be hard to know how to respond in such circumstances, other than repeating the same answer as before, which is presumably not much help.
Jun 8 '10 #10
jacc14
116 100+
@NeoPa
Hi. Sorry I should clarify. When I reviewed Jim's zipped file in post #8 I realised what I had done which was to have added "As date" in the module. By doing this I actually converted Jims solution to text which does work still. However I now need to add lots of these times together and because its text it wont let me.
I hope this clarifys the matter.
Thanks
Christine
Jun 8 '10 #11
Jim Doherty
897 Expert 512MB
@jacc14
Christine, the solution to this is in the elements of that function already.

If as I would expect you are embedding the function in your query to give you a textual representation of hours and minutes.

If all you are needing in addition is a summation of the time involved just look at the datediff element in isolation. You will understand that the datediff function returns the calculated minutes for the specific period outlined by the parameters passed in.

So, if you had a column of those minutes (as was shown in my most recent upload) you could sum that column of minutes and calculate the overall hours and minutes using the last element of the function namely :
Expand|Select|Wrap|Line Numbers
  1. Format(Int(prd / 60), "00") & ":" & Format(prd Mod 60, "00")
The mechanics of this is fairly straightforward to do I just dont know your layout there.

I have attached a revised file that includes a TotalTime function look at the form for the extra bits at the bottom
Attached Files
File Type: zip jacc14.zip (24.6 KB, 112 views)
Jun 8 '10 #12
NeoPa
32,556 Expert Mod 16PB
Jim,

I don't know if you noticed, but you seem to have submitted your post before finishing the line of code.

I edited it to put the tags in, but the code is the same as you posted.
Jun 9 '10 #13
Jim Doherty
897 Expert 512MB
@NeoPa
Thanks Adrian......am in too much of a rush lately obviously the paste and post didnt do it, have revised it now :)
Jun 9 '10 #14
NeoPa
32,556 Expert Mod 16PB
Excellent. I was sure it must have been something as simple as that :)
Jun 9 '10 #15
jacc14
116 100+
@Jim Doherty
Excellant Jim .
thanks for all your assistance.

All the best Christine
Jun 10 '10 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: alexis | last post by:
Hi, In a form I have the curent date <input name="datetoday" type="hidden" value="<? echo date("d/m/Y"); ?>"> and <input type=text name="datebox" size=15> The date format is d/m/Y...
9
by: Rich | last post by:
Thanks for the Help in my previous post. I've been working on this and it's almost what I want. I want to obtain the user's current age by comparing their date of birth (user inputs) to the...
1
by: Manny Chohan | last post by:
i have two date fields for event start date and end date. I let the user enter two on create event page. on submit, i need to compare if the end date is earlier than start date or not. How can i...
5
by: Tom | last post by:
It appears that you can't compare two dates in DotNet. You must use ToString and compare the strings. Is that the only reliable way? Try this: Dim dteOne As Date =...
5
by: DW | last post by:
I have a query in Access 2003 that has the following criteria SELECT tblOrder.SessionDate, tblMenus.Item_Name, tblOrder.Type, Format$(tblorder!SessionDate,"Short Time") AS SessionTime,...
8
by: RobinDiederen | last post by:
Suppose, I want to compare 2 dates in an SQL query, running from Access (from VB code) like this: "SELECT * FROM Plan WHERE Date1 > Date2" Will that work? Thanks in advance!
1
by: jackiepajo | last post by:
Please help me, i want to compare dates between two check boxes like i want to compare inputs check in date and check out date to those stored in the database. please help....
2
by: rocket | last post by:
I need to make a query that will be run everyday that will send an email on a persons birthday. The query needs to compare the day and month of his/her birthday to the current date, and if...
4
by: xiaowei | last post by:
I m writing some VB codes for my access database... Now I have a table which contains a "editDate" field, its format is "26/06/2007 11:12:34 AM", then I created a form which would ask the user to...
2
by: site | last post by:
Have to compare dates of two rows in one table Input table COL1| COL2| COL3| COL4 1| A| 1-Jan-07| 6-Feb-08 2| A| 7-Feb-08| 31-Mar-08 3| A| 1-Jan-09| NULL SQL code to compare COL 4 date (Row...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.