Goal Seek using Time

[laya1024]

Hi-

I am doing a project that I am having difficulty with. Hope you ca
help.

I am trying to use goal seek to see with the fourth quarter targe
needs to be in order to meet a yearly goal. The measurement is averag
time on a call. So the data looks like:

Q1 - 00:03:52
Q2 - 00:05:25
Q3 - 00:04:18
Q4 -
Average needs to be 00:04:30 for all four quarters to hit target. So
need the output to be a time in the Q4 cell. in the average cell,
used a formula to get the average of all 4 quarters. I used goal see
with the set value being the average cell, the to value being 00:04:3
and by changing cell is the empty Q4 cell. When I press ok, the goa
seek status box appears and the target value has changed to 0.003125
and current value is 0:03:24. A 0 appears in the Q4 cell. I wa
expecting the Q4 cell to have a format of hh:mm:ss and it very wel
should be greater than zero. And the average should be 00:04:30 no
00:03:24. What am I doing wrong and how can I fix it? I already trie
to convert to seconds, that wasn't quite working either. HELP!
Thanks

 

[Joel]

You need to format the number 0.003125 which represents a fraction of a day.

If A1 = 0.003125

In spreadsheet
=Text(A1,"hh:mm:ss")

In VBA
MyTime = 0.003125
TextTime = format(MyTime,"hh:mm:ss")

 

[Sheeloo]

First I also got the same result as you.

Then I started with a new shee, formtted col A as Cutsom->h:mm:ss, entered
the values you have given and got the right answer - 4:25:00 in cell B4...

btw 0.003125 translates to 1/0/1900 12:04:30 AM...
As you probably know Excel stores dates as a number with 1 being 1-Jan-1900
so when you enter 03:52 in a cell you are actually entering 1/0/1900
12:03:52 AM