+ Reply to Thread
Results 1 to 3 of 3

Question on how to show 80hrs in a cell ? using =time(x,x,x)

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-02-2019
    Location
    Ottawa,Ontario
    MS-Off Ver
    365
    Posts
    183

    Question on how to show 80hrs in a cell ? using =time(x,x,x)

    Hi,

    For purposes of a timesheet and overtime I have a cell that will reference 8hrs as a standard day using forumla =time(8,0,0) and cell formated custom [h]mm. I am now needing another cell to show 80hrs in a cell, but =time(80,0,0) doesn't work for me.

    Is there something I am missing ? when I do =time(80,0,0) and using same custom format it onlys as 8:00 just like the 8hrs.

    Im stumped

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,473

    Re: Question on how to show 80hrs in a cell ? using =time(x,x,x)

    According to the help file (https://support.microsoft.com/en-us/...e-747d0b8d5457 ) TIME() cannot return a value greater than 1 day. When you put 80 in the hours argument, Excel divides by 24 (80/24=3 days 8 hours) and returns only the remainder -- the 8 hours part.

    In order to get something representing 80 hours, you will need to enter using something other than the TIME() function. I can enter 80:00:00 and Excel and it will recognize this as 80 hours (3 1/3 days). If you can convert the number of hours to days (the basic unit of Excel's date time serial number system), then you can enter that -- so 80 hours could be entered as 3 1/3 or 3.3333333333 or similar. You could use the CONVERT() function (or equivalent) =CONVERT(80,"hr","day").

    Several options, but just know that you cannot use the TIME() function for times larger than 1 day.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Question on how to show 80hrs in a cell ? using =time(x,x,x)

    @northernwarrior.... You can simply write 80:0 into a cell formatted as Custom [h]:mm, or "80:0" with double-quotes in a numerical expression (e.g. A1-"80:0").

    Use the numerical expression --"80:0" or VALUE("80:0") -- not TIMEVALUE -- if you need the constant to stand alone, for example as parameter (e.g. VLOOKUP(--"80:0", A1:B100, 2, TRUE)).

    That form works as input for times up to 9999:59:59.999 (416 days 15h 59m 59.999s).

    I say "as input" because the format [h]:mm:ss.000 can display a longer range of time, up to 71003183:59:59.999. That is 2958465 days 23h 59m 59.999s, where 2958465 is the serial number for 12/31/9999, the largerst date that Excel formats with a date or time format.
    Last edited by curiouscat408; 07-23-2022 at 08:42 PM.

+ 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. How to code vba or input formula to show real time and show status compare time
    By Peer2021 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2021, 05:35 AM
  2. Replies: 3
    Last Post: 04-12-2020, 12:09 PM
  3. Replies: 0
    Last Post: 08-30-2018, 02:42 PM
  4. [SOLVED] Newb question. If cell is above X%, then next cell to show 5% of next cell
    By koalamotorsport in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-13-2014, 12:14 PM
  5. [SOLVED] Show the date and time in a specific cell of last time we run a macro
    By dragon_m0nsta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2012, 04:49 AM
  6. how to convert 6:80hrs imto 7:20hrs
    By Pritesh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2007, 04:23 PM
  7. Simple Question... show + sign in cell
    By CC_mfc in forum Excel General
    Replies: 3
    Last Post: 01-31-2007, 11:49 AM

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