+ Reply to Thread
Results 1 to 5 of 5

Dividing af formula for elapsed time with a fixed(static) time

  1. #1
    Registered User
    Join Date
    10-30-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010
    Posts
    2

    Dividing af formula for elapsed time with a fixed(static) time

    Hi all,

    First post here, and in a bit of a tight spot.
    I have a formula:
    =NOW()-TIME(8;0;0) to calculate time elapsed at work
    that I want to divide by hours we are open at work; a fixed value of 12:00:00 (hrs)

    When I divide them and want it in percent, I get a huge number (8388459,8% e.g.)

    If I plot the formula's result in manually I get the correct percentage out. Both cells are the same format in time.

    Could you help me out? I've searched as much as I could, and the only thing I find is a macro to plot the result of =NOW()-TIME(8;0;0) in another cell.

    Thanks!

    Christopher

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dividing af formula for elapsed time with a fixed(static) time

    Instead of using NOW() use NOW()-INT(NOW()).

    NOW() returns the date plus the time.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Dividing af formula for elapsed time with a fixed(static) time

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    10-30-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010
    Posts
    2

    Re: Dividing af formula for elapsed time with a fixed(static) time

    Thanks guys it worked! I used the =NOW()-INT(NOW())-TIME(8;0;0) which worked. The two formulas you mentioned, Olly, gave me some erroneous times.
    Thanks again

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Dividing af formula for elapsed time with a fixed(static) time

    You're welcome. Thanks for the feedback!

+ 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. [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
  2. Excel Time and Elapsed time formula
    By Shadow Optic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2013, 04:49 PM
  3. Using start/end time fields on userform to populate elapsed time on spreadsheet
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2011, 05:07 PM
  4. Replies: 2
    Last Post: 04-27-2011, 08:21 AM
  5. time formula with fixed travel time
    By loudguy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2009, 06:33 AM

Tags for this Thread

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