Hi

adding in this extra "0:00" time data. Weird.

You have try this example first with my first example I posted (with time)
Select the cells and change the format(Ctrl-1 Format Cells)

If you only want the date then change Now to Date in the code and remove the hh:mm

ultimate goal is

In J
=(80000-H6)/83

In K you can use this formula
=I6+ROUND(J6,0)
And format the cell as you want



--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message oups.com...
Ron de Bruin wrote:

Place the code in the Sheet module

[ ...snip... ]

Mr. de Bruin...thanks for your help.

You got me in the right direction. I made a small change to the code
to allow for different cell names and removed the "hh:mm" information.
I am, however, seeing something odd. Here's a screen snapshot:

<IMG src="http://www.banchy.net/misc/excel/scr1a.jpg"

In this example, at cells "I9" and "I11", the VB code you gave me is
adding in this extra "0:00" time data. Weird.

Here's the code as I modified it:

<IMG src="http://www.banchy.net/misc/excel/scr2a.jpg"

I wish I had the time to learn a bit about VB. I know a bit of perl
and do a lot of unix Bourne scripting...and probably know just enough
to be dangerous in other arenas. I know nothing about Excel and VB and
was assigned this task by someone who thinks that if I'm a computer
guy, then I certainly must know everything there ever is to know about
computers! Of course, I'm really a hardware guy who primarily works
with unix/linux... :)

Anyway...any ideas? In case you haven't also figured it out, my
ultimate goal is to have my "Estimated EOS Date" to reflect the
estimated date a vehicle is to be retired. An assumption is to be made
that my organization puts 2500 miles on a vehicle per month (this
translates to 83 miles per day, on average). Another assumption is that
at 80,000 miles, our vehicles are removed from service. Knowing that
data and knowing the current mileage of the vehicle, I should
(hopefully) be able to convince Excel to calculate the estimated
vehicle date of death by doing something like this:

if today, 03/16/2005, the vehicle is at 20000 miles
and the vehicle date of death is 80000 miles
and each calendar day accounts for 83 miles
then the estimated vehicle retirement is
80,000 - 20,000 = 60,000 miles left to go, then
60,000 miles / 83 day = 722 days from today until retirement

I would then want Excel to translate 722 days from today back into a
mm-dd-yy value (ie, March 8, 2007).

Gawd....I wish I knew VB/Excel. Whatya think? :)