1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel: Overflow error when converting decimal to date

Discussion in 'Business Applications' started by YanickLacombe, Sep 13, 2009.

Thread Status:
Not open for further replies.
  1. YanickLacombe

    YanickLacombe Thread Starter

    Joined:
    Sep 13, 2009
    Messages:
    2
    Hi,

    Here is my problem. I'm making a countdown timer in Excel. The process is working fine, but my problem is when I call my Sub to set the time at which I want the countdown to end, I receive the following error:
    -------------------
    Run-time error '6':

    Overflow
    -------------------

    Here is my code:

    Dim TimerEnd as Date
    Dim WorldPop as Integer 'This value is picked from my Excel worksheet

    TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

    Note: WorldPop was tested and the value from the cell is really loaded because I was able to modify another cell value by assigning WorldPop value to it.
    ---------------------------------------------------------------------

    Basically, I store in TimerEnd the time at which I want the countdown to reach 0 (obtained by doing TimerEnd - Now() )

    The 1st part of the expression:
    (1500 - (3 * WorldPop / 8)) determines in how many seconds the timer should end

    The 2nd part of the expression:
    / (60 * 60 * 24) divides the 1st expression to obtain the seconds in decimal value which is added to Now() to get the end time.

    What I don't understand is that when I type this expression in a cell of my worksheet that have been formated as date:

    =NOW()+(1500-(3*850/8))/(60*60*24)
    Note: 850 is just a random number which I tested the result. It can be any number ranging from 0 to 2000

    It gives me a valid time in the cell.

    What am I doing wrong so that in the code of my subroutine it gives me Overflow?

    Is there a work around to this?

    I'm working on Excel 2003 on Windows XP SP2

    Thank you
    Yanick
     
  2. YanickLacombe

    YanickLacombe Thread Starter

    Joined:
    Sep 13, 2009
    Messages:
    2
    I found the problem.

    The following expression was too long:
    TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / (60 * 60 * 24))

    I simplified it to:
    TimerEnd = Now() + ((1500 - (3 * WorldPop / 8)) / 86400)

    and it resolved my problem.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/860513

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice