# Thread: Date manipulation in Access

1. Registered User
Join Date
Sep 2007
Posts
8

## Unanswered: Date manipulation in Access

Hi All,
I am a new member and I would like help and/or suggestion or a solution for:

A ticket opens on 5/9/07 13:38 and closes on 7/9/07 10:30
I found ways to calculate correectly the difference of days and time
but I want to analyze the day/time difference as follows:

Code:
```5/9/2007 13:38  10:21:59       5/9/2007 23:59	56,81%	24:00:00
6/9/2007  0:00  23:59:59       6/9/2007 23:59	 0,00%  24:00:00
7/9/2007  0:00  00:00:00       7/9/2007 10:30	56,25%	24:00:00```
For the time being I am doing this manually in Excel !!!
Last edited by gvee; 09-13-07 at 06:36. Reason: [CODE] tags added to retain formatting.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
What are the percentages and how are they calculated.
What is the 24:00:00?

Please try clarify what you are trying to do.

3. Registered User
Join Date
Sep 2007
Posts
8

## Date manipulation in Access

Thank you for the fast response
Sorry if I wasn't so clear in my first posting
If a ticket opens at 5/9/2007 13:38 and closes at 7/9/2007 10:30
I have to calculate the downtime from the ticket open time (10:21) till 24:00:00 (midnight - end of the day).
Calculate the next day (6/9/20070 from start of day (00:00) till 24:00:00
and from 00:00 of 7/9/2007 tilll closure of ticket (at 10:30)
If the ticket open and close in the same day I don't have problem.

With the following example starts my problem
5/9/2007 13:38 10:21:59 5/9/2007 24:00:00 = 10hrs 22min
6/9/2007 00:00 6/9/2007 24:00:00 = 24 hrs
7/9/2007 00:00 7/9/2007 10:30 = 10hrs 30 min
(Of course a solution can take more than three days to be solved)
Thank you

4. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
I did the following in the Immediate window. It might point you in the direction you want to go.

aa=#9/15/07 13:00:00#
?aa
9/15/2007 1:00:00 PM

bb=now()
?bb
9/17/2007 12:15:41 PM

?bb-aa
1.96922453703883 (that's a Double precision number. Dates are stored in Access in that type)

cc=int(bb-aa)
?cc
1 (that's the amount of days, and it's Numeric. You can multiply by 24 for hours)

?format((bb-aa) - int(bb-aa), "hh:nn:ss")
23:15:41 (that's everything but whole days. It's a string, however, so any mathematical manipulation will require the CDate() function)

Hope this helps.

Sam

5. Registered User
Join Date
Sep 2007
Posts
8
Dear All,

I know it is difficult to explain but I will try.
I need the hours from the ticket open time to ticket close time.
In case the close time lies in a different date I need to calculate the downtime percentage for EACH elapsed DAY SEPARETELY
For this
Ticket open time Ticket close time
5/9/2007 13:38 10:21:59 7/9/2007 10:30
__________________________________________________ ______________
I need the following

From 5/9/2007 13:38 10:21:59 Till 5/9/2007 24:00:00 = 10hrs 22min
From 6/9/2007 00:00 Till 6/9/2007 24:00:00 = 24 hrs
From 7/9/2007 00:00 Till 7/9/2007 10:30 = 10hrs 30 min
Thank you

6. Grand Poobah
Join Date
May 2003
Location
Dallas
Posts
820
This function does return the difference in hours between two dates
it needsd a little working to format to minutes, etc but it may help
Public Function GetTimeinHours()
Dim TheDate As Date
Dim Msg
TheDate = InputBox("Enter a date")
Msg = "Hours from today: " & DateDiff("h", Now, TheDate)
MsgBox Msg
End Function

try this - it is simple and oif the result is what you are looking for or close than let us know - we can refine it

7. Registered User
Join Date
Sep 2007
Posts
8

## Date Manipulation In Access

Dear All,

Ticket open Ticket close
5/9/2007 10:21 7/9/2007 10:30

The total hours from ticket open to ticket close are 44hrs 52 min
I can calculate this amount of time and even the elapsed days in between.

I want to split the total hours (44:52:00) per day
e.g.
How many hours for 5/9/2007 ?
How many hours for 6/9/2007 ?
How many hours for 7/9/2007 ?
Is it possible?
I want to Thank you for your efforts till now.

8. Registered User
Join Date
Nov 2002
Posts
272
Originally Posted by SROSSI
Ticket open Ticket close
5/9/2007 10:21 7/9/2007 10:30

The total hours from ticket open to ticket close are 44hrs 52 min
Why isn't it 48 hours and 9 minutes?

9. Registered User
Join Date
Nov 2002
Posts
272
Anyway, you need to iterate over all days that the ticket is open.
For each day, you need to calculate the difference between:
- The end of that day OR the [Ticket close], whichever is earlier
and
- The start of that day OR the [Ticket open], whichever is later

Example:
Ticket open Ticket close
5/9/2007 10:21 7/9/2007 10:30

For 5/9:
The end of the day is earlier than the closing of the ticket, and the opening of the ticket is later than the start of the day, so for this date the elasped time is the difference between the end of the day (6/9/2007 0:00) and 5/9/2007 10:21, and that's 13h39m.

For 6/9:
The end of the day is earlier than the closing of the ticket, and the start of the day is later than opening of the ticket, so for this date the elasped time is t he difference between the end of the day and the start of the day, obviously 24h00m.

For 7/9:
The closing of the ticket is earlier than the end of the day, and the start of the day is later than opening of the ticket, so for this date the elasped time is the difference between closing of the ticket (10:30) and the start of the day, 10h30m.

10. Registered User
Join Date
Sep 2007
Posts
8

## Date manipulation in Access

Hi IVON,
Sorry for the sum of hours. You are right. It is 48h 09m.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Been Here done this

As you want the Total Hours totals

Work out the Total MINs

then div 60

Int() the Differnace so we get the hole hours

subtrach the int() differnace which is min left

know join then

Written it hard than writting the code

[code]
Function Howlong(St, fin)
min = DateDiff("N", St, fin)
hh = min / 60
diff = Int(hh) * 60
mm = min - diff
Howlong = Format(Int(hh), "00") & ":" & Format(Int(mm), "00")
End Function
[\code]

to use

aa = Howlong(#7/9/2007 10:21:00 AM#, #7/9/2007 10:30:00 AM#)
aa = 00:09
aa = Howlong(#7/9/2007 10:21:00 AM#, #7/10/2007 10:30:00 AM#)
aa= 24:09
aa = Howlong(#7/9/2007 10:21:00 AM#, #7/10/2007 10:41:00 AM#)
aa = 24:20
to use in a query

HLONG:Howlong([datetimefeild],[datetimefeild])

12. Registered User
Join Date
Sep 2007
Posts
8
Dear MYLE

In the following example :
Howlong(#7/9/2007 10:21:00 AM#, #7/10/2007 11:41:00 AM#)
the result would be = 25:20
What if I want to have the hours of every day separetely?
e.g. for 7/9/2007 13:39 and
for 7/10/2007 11:41

Thank you

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
as It get broken down to the mins first them
min hold the total mins bewteeen the to date and times

aa = Howlong(#7/9/2007 1:39:00 PM#, #7/10/2007 11:41:00 AM#)

aa= 22:02
Last edited by myle; 10-02-07 at 15:09.

14. Registered User
Join Date
Sep 2007
Posts
8
Hi All,
I have a date field, filled with the value "3/9/2007 4:50:00 p.m."
How can I calculate the remaining hours
from "3/9/2007 04:50:00 p.m." till "3/9/2007 23:59:59"
and represented as 07:09 ?

THANK YOU

Join Date
Nov 2004
Location
out on a limb
Posts
13,692