Compute from a derived formula

Hi
I have cells in my XL with values like "2d 3h";since I cannot sum them
up, I tried "SUBSTITUTE" the d with *8.
For example, if the cell value is 2d it would look like 2*8.
Now I would like to just use this value to compute, eventually to get
2*8=16.
Any idea how I can achieve this?
Thanks
hari.

yeah get rid of excel and use a database.
you can do a lot more powerful stuff with databases

Are all your values a single digit followed by single character? Do you
need to distinguish between the different letters? Give us some more
examples if not, and are there any patterns to your data?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<[email protected]> wrote in message
news:[email protected]...
> Hi
> I have cells in my XL with values like "2d 3h";since I cannot sum them
> up, I tried "SUBSTITUTE" the d with *8.
> For example, if the cell value is 2d it would look like 2*8.
> Now I would like to just use this value to compute, eventually to get
> 2*8=16.
> Any idea how I can achieve this?
> Thanks
> hari.
>

Ken,
Max would be 2 digits before the letters.
12d 13h 30m
all the data would be in this pattern.
Thanks
Hari

Hi Hari

if you didn't have any hours would it be written like

1d 0h 10m

or 1d 10m

Cheers
JulieD

<[email protected]> wrote in message
news:[email protected]...
> Ken,
> Max would be 2 digits before the letters.
> 12d 13h 30m
> all the data would be in this pattern.
> Thanks
> Hari
>

Julie,
It would be like
1d 0h 10m
Thanks
Hari

Hi Hari

the only way i can think of doing it is to use a macro along the lines of
the one below - this will change all the cells you've select into a decimal
number, using a day as being 8 hours. NOTE this code assumes that each cell
has a "d", "h" and "m" element in it.

Sub changetime()
For Each c In Selection
c.Value = Application.WorksheetFunction.Substitute(c.Value, "d ",
"*480+")
c.Value = Application.WorksheetFunction.Substitute(c.Value, "h ",
"*60+")
c.Value = Application.WorksheetFunction.Substitute(c.Value, "m", "")
c.Value = "=" & c.Value
c.Value = c.Value / 60
Next
End Sub

---
to use the code, right mouse click on a sheet tab, choose view code, this
will display the VBE window. Choose insert module, copy & paste the code
in - if any lines go red, click at the end of them and press delete (this
should fix any line wrap problems).

to run the code, select the cells you want to change and choose tools /
macros / macro - find the changetime macro and click run.

other's might have a better solution but in my testing this seems to work.

Cheers
JulieD


<[email protected]> wrote in message
news:[email protected]...
> Julie,
> It would be like
> 1d 0h 10m
> Thanks
> Hari
>