# Solved: Excel: Overflow error when converting decimal to date

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

Not open for further replies.

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

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