Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2007
    Posts
    8

    Question 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 !!!
    Thank you in advance.
    Last edited by gvee; 09-13-07 at 06:36. Reason: [CODE] tags added to retain formatting.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What are the percentages and how are they calculated.
    What is the 24:00:00?

    Please try clarify what you are trying to do.
    George
    Home | Blog

  3. #3
    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. #4
    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. #5
    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. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    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
    Dale Houston, TX

  7. #7
    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. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote 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. #9
    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. #10
    Join Date
    Sep 2007
    Posts
    8

    Red face Date manipulation in Access

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

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    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])
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  12. #12
    Join Date
    Sep 2007
    Posts
    8
    Dear MYLE
    Your reply was very helpful to sum the hours.

    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

  13. #13
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    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.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  14. #14
    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

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    datediff function is your friend
    then display the result using an appropriate format() mask

    the helps system should be your "bestest" buddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •