Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10

    Convert elapsed time to number format


    Hello!

    I have read a lot of discussions about this topic, with a number of advanced solutions, and I have tried them all out, but of one or another reason, I can not find anything that works.

    Situation: I need to calculate elapsed time into a number format, and I also need to handle the situation with passing midnight.
    Example: Start time: 23:30 End time: 01:00 (The next day), which is 1 hour and 30 minutes, to be displayed as this: 1.5

    I have the following:
    [Start time] - Date/Time format - short time - does not include the date
    [End time] - Date/Time format - short time - does not include the date

    In the query I have a simple code that handles passing midnight, but not the convertion to number format.

    Medgått tid: Format([Tid fra]-1-[Tid til];"Kort klokkeslett"), as in english would be:
    Elapsed time: Format([Start time]-1-[End time];"Short time")

    This code gives me: (From 23:30 to 01:00) 1:30 as result (as is also correct) - but I want the result to be 1.5
    Besides all the other advanced solutions I have found, I have also tried to change "Short time" (in the code) with; "Number", but this will not to the trick.
    Hope anyone out there have a real solution.

    Børge
    Norway

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Would be simplified if date part were available. Otherwise, take the elapsed time string and parse it to recalculate to decimal hours. Let x represent that string.

    Val(x) + Right(x,2)/60
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    post deleted - misread the original question

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Two examples From the immediate window
    Code:
    ?Cdbl(#01:30#)*24
     1.5 
    
    ?Cdbl(#03:15#)*24
     3.25 
    You should be able to use that with your elapsed time calculation to get the desired results
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Problem with that is their calculation using Format is a string value and CDbl errors. Without Format(), the expression fails for periods that don't cross midnight. So this works:

    CDbl(CDate(Format([Start time]-1-[End time],"Short Time")))*24
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I was a betting guy, I'd bet that the record contains one or more date fields, which would probably make any of this completely unnecessary. Even if you're only using Time, I think it makes no sense to not include the date with time. So much easier to format to show only the time portion and certainly much easier to deal with clock time that spans different dates. Not having the date as part of the data is only making this unnecessarily harder.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10
    Hi June7!

    That last one really did the trick!
    This was a real Christmas gift!
    I struggled a bit with the comma separator, but I finally got it.

    I though have another question.
    It probably should be posted under forms, but since it is a following up question, I take my chance and post it here.

    When I start typing into my form, the Elapsed time field shows: #Feil (Norwegian). I would guess the english substitute wil be something like this: #Error.
    I don't really mind, but when coming so far, it would be really nice to know if there is a way to avoid this.
    The Elapsed time field is deactivated and locked. I have also tried out different corresponding formats for the Elapsed time Query field, but with no luck.

    The form is a simple one. No macros or anything like this. It does however contain two dropdown lists. One simple list from the main table, and one picking up information (two fields) from a related table.

    Børge

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, forgot about foreign database using semi-colon instead of comma.

    #Error is probably because time fields are null and Format expression returns an empty string. CDate (as do all number conversion functions) errors on empty string. One way to prevent the #Error is to handle Null with Nz to provide an alternate value.

    CDbl(CDate(Format(Nz([Start time],0)-1-Nz([End time],0),"Short Time")))*24
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No need for Format or CDate.
    Here's a much simpler version which will work whether or not the end time is the following day.
    Code:
    ElapsedTime: (IIf([End Time]<[Start Time],1,0)+CDbl([End Time]-[Start Time]))*24
    I assume you will need ; instead of , in the IIF expression

    Obviously, like all previous suggestions, it will give an incorrect result if the elapsed time is more than 24 hours.
    However, if that's not an issue, I'd use this instead

    As for using Nz, whilst it will prevent an error if one of the times is blank (null), doing so will give misleading results.
    If missing times are an issue, I'd probably wrap the above function using IsError and if that's true set the result to null.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10
    Excellent!

    Thanks!

  11. #11
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10
    Hello there!

    I have tried both solutions now.

    Both removes the error message wich is excellent!
    June7's solution returns an inadequate number in the "End time" field, when I start typing in the "Start time" field. And it gives also some challenges with the amount of decimals. But that can be handled.

    isladogs's solution returns likewise a number in the "End time" field, but not before exiting "Start time"/entering "End time". This number is excactly one hour earlier than the time entered in "Start time", and, so to speak, in a way corresponds to the formula. When exiting "End time" the correct result appear in "Elapsed time". This solution also decreases the number of decimals down to 2 automatically, wich is the format that is needed.

    What could be convenient in this situation, was to tell Access in some way to hide the information in the "End time" field/or "do nothing else" than accept the typing. And likewise with the "Elapsed time" field, until access had actually calculated the elapsed time.


    Missing times and Expanding 24 hours is not an issue here.

    Both solutions works fine for me, as long as it is only for private use. If this was a part of a product that was going to be used in a bigger scale, I would have had to dig somewhat deeper into this. Now You'll all saving me from using a lot of unnecessary time calculating this manually with my calculator for each record - it can be up to 15 for each working day!

    Great thanks to all!

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm confused by parts of your answer.
    The result is exactly correct in my tests. No idea what the 1 hour earlier comment means.

    Are you typing times direct into a query? If so, you will get an error unless both fields have been entered. That's why I mentioned using IsError as well.

    However all data entry should be in forms. You would use code to first check whether both times had been entered before calculating the elapsed time.
    Finally as its your own application, then as already mentioned in an earlier post, you should store both the date and the time to avoid any complications in the elapsed time expression. It would then be a simple calculation =24*([End Time]-[Start Time])
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ICBSprod's Avatar
    ICBSprod is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Norway
    Posts
    10
    Hi!
    What i tried to explain was this:

    In my form, let's say I type in 12:00 in "Start time" field.
    The next field (in tab order) is the "End time" field.
    Pressing my tab once, takes me out of "Start time" and into "End time".
    When "End time" get focus, this field displays 11:00.
    No calculations are made yet. The "Elapsed time" field displays "0" as expected.
    Now I type in/overwrite 11:00 with the correct time. When I tab myself out of "End time" and into the next field (not "Elapsed time" - it is not active, it is locked and has no tab) the calculation is made, and "End time" displays the correct elapsed time.
    If I type 11:15 in Start time, End time displays 10:15, before i correct it, and so on.
    Never seen this behaviour before, but I can live with it as long as the calculations are correct.
    It's a simple form, based on one query. Behind the Query is one main table and one related table.

    To Your comment about using both date and time. I think it is more convenient to only type in the time, not having to type in both date and time in each field, for every record. I have a separate date field, where I write in the date once, then just pick it (Ctrl+Asterisk) from the previous record. Next day, write in the new date in the first record, then pick it up for the next records.
    But maybe I am overthinking here, or think it all wrong.

    Børge

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A date value and a time value can be added to have a full date/time value that can be used in DateDiff() function to calculate elapsed time for any length period, crossing midnight or not.

    So if you have StartDate and EndDate as well as StartTime and EndTime:

    DateDiff("n", [StartDate]+[StartTime], [EndDate]+[EndTime])/60

    If any field is Null, result is Null. No funky formula, no formatting, no conversions, and no #Error.

    Times of exactly midnight should have 0 value in field. Set time fields to have 0 default or use Nz() to supply 0 if times are null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @OP
    The behaviour you describe is nothing to do with the code I supplied.
    Check the properties such as Default value for your End Date field to find out why this is happening
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert Number to time format
    By Nalaka in forum Access
    Replies: 7
    Last Post: 01-14-2019, 04:42 AM
  2. Replies: 1
    Last Post: 08-12-2014, 09:48 PM
  3. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  4. Convert Number to Date/Time
    By Jerseynjphillypa in forum Queries
    Replies: 10
    Last Post: 06-13-2012, 12:33 PM
  5. Replies: 1
    Last Post: 08-07-2011, 07:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums