+ Reply to Thread
Results 1 to 9 of 9

Converting Numerical Values to Time to then calculate duration in decimal format

  1. #1
    Registered User
    Join Date
    07-05-2013
    Location
    Sierra Vista, AZ
    MS-Off Ver
    Excel 2010
    Posts
    10

    Converting Numerical Values to Time to then calculate duration in decimal format

    So I'm re-working an excel spreadsheet and want to set it to where co-workers can enter time as just 1340 (24-hour clock) and have it convert it to 13:40 in the cell. I am then looking to be able to calculate the duration between the two times and have the value calculated to time in decimal. So if between 1500 (15:00) to 1824 (18:24) TASK A is completed, it would be 3.4 hours. The current formula is as follows and the problem is that with the ROUNDUP function, it skews the time to where from 1600-1618 is calculating to a 0.4 whereas it should be a 0.3 (18 minutes...).
    =ROUNDUP((((MOD(B63,100)/60+INT(B63/100))/24)-((MOD(A63,100)/60+INT(A63/100))/24)+IF(B63<A63, 1,0))*24,1)

    Times should be calculating as such:
    1-6 minutes =.1
    7-12 minutes =.2
    13-18 minutes= .3
    19-24 minutes= .4
    25-30 minutes= .5
    31-36 minutes= .6
    37-42 minutes= .7
    43-48 minutes= .8
    49-54 minutes= .9
    55-60 minutes= 1.0

    The cell formatting for the times themselves is Codigo Postal. I am hoping to keep this as cell formatting and formulas as I am not familiar with VBA macros, etc. TIA
    Last edited by andy867; 05-15-2018 at 10:37 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    My 1st question would be - why mess with this? Time is time and excel knows how to run calcs with it.

    In case you didnt already know, Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-05-2013
    Location
    Sierra Vista, AZ
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    The spreadsheet is for calculating flying time, lost flying time, ground training, etc, which is measured in decimal time format (ie 3.4 hours). So if takeoff is at 1930 and landing is at 0525, the total flight time is calculated at 10.0 hours (9 hrs, 55 minutes).

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

  5. #5
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    485

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    Try this (lightly tested):

    =CEILING(MROUND(MOD(TEXT(B63,"00\:00")-TEXT(A63,"00\:00"),1),"0:01"),"0:06")*24

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,914

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    Try this:

    =CEILING(ROUND((TEXT(B63,"0\:00")-TEXT(A63,"0\:00")+(B63<A63))*24,3),0.1)

  7. #7
    Registered User
    Join Date
    07-05-2013
    Location
    Sierra Vista, AZ
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    It appears that the CEILING function from Phuocam appears to have created the result I was looking for. Just had to modify the initial cells from Codigo Postal to 0\:00 and add the new formula and was rocking. Thanks everyone for your quick replies and input!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    Glad you got where you wanted to be

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Converting Numerical Values to Time to then calculate duration in decimal format

    Try this

    A2= Start Time, B2 =ENd time
    Formula in C2

    =CEILING((B2-A2)*24,0.1)
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate time difference in decimal format
    By kunal.patni in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-28-2017, 08:39 AM
  2. Converting time in hh:mm:ss format to decimal format
    By shanekerr in forum Excel General
    Replies: 4
    Last Post: 05-28-2015, 03:25 AM
  3. [SOLVED] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  4. [SOLVED] Converting time hh:mm:ss into decimal format? mm.ss
    By moxman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-16-2013, 06:53 PM
  5. Help in converting duration to hrs format
    By gloom52 in forum Excel General
    Replies: 3
    Last Post: 09-02-2009, 06:30 PM
  6. Converting Time Sheet Into Decimal Format
    By TropicalRain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2007, 02:30 AM
  7. Replies: 1
    Last Post: 07-30-2005, 06:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1