Results 1 to 5 of 5

I need something in between DateValue() and TimeValue()

Threaded View

  1. #1
    Registered User
    Join Date
    06-26-2006
    Posts
    44

    I need something in between DateValue() and TimeValue()

    I have TimeValue() operating on some strings and then I add the values up. Then I need something that does this pseudocode:
    If(total>"40 hours",this,that other thing)
    Here's the problem: how do I test for "greater than 40 hours"?

    TimeValue() only works for times between 0 hours and 24 hours. Anything more and it acts like a modulus and just loops around again. So TimeValue("40:00") gives the same result as TimeValue("16:00").

    DateValue() basically has an embedded RoundDown() function. DateValue("40:00") gives "1" (because it's less than 48 hours or two days) while DateValue("50:00") gives "2" (because it's more than 48 hours but less than 72 hours).

    I suppose I could do something like:
    =If(total>(TimeValue("20:00")*2),this,that other thing) or
    =If(total>(TimeValue("20:00")+TimeValue("20:00")),this,that other thing) -- these are the same thing, just one might be more readable at a glance
    But that seems really clunky. Isn't there anything in between DateValue() and TimeValue(), something that will convert a time greater than 24 hours, but for which I don't want to round to complete days?
    Last edited by Banaticus; 09-27-2014 at 07:06 PM. Reason: solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to join datevalue and timevalue into one
    By Quintin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2014, 05:49 AM
  2. Timevalue
    By merl4 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-02-2013, 08:40 AM
  3. timevalue
    By tomfromportage in forum Excel General
    Replies: 2
    Last Post: 03-24-2012, 05:20 PM
  4. Ontime Now + TimeValue
    By trinn89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2011, 07:53 PM
  5. OnTime / TimeValue
    By Myriam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2005, 07:28 PM

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