Compute from a derived formula

  • Thread starter Thread starter harisri
  • Start date Start date
H

harisri

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?
 
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
 
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
 
Back
Top