Representing a column in minutes...

misterg · May 22nd, 2002, 04:51 AM

Good day to you all

misterg · May 22nd, 2002, 04:54 AM

Ok, pressed enter too quickly there, but hello anyway!

Right...my question for today is a relatively simple sounding one.

I have a formula written to represent how much time has been taken doing a task based on hours worked and work carried out.
Just trying to represent the "Amount of average time taken on a case" but...cannot find the correct format to represent the answer to the formula as minutes.

It keeps displaying the answer as a fraction of 100, not 60.

Any ideas how i can get around this rather simple sounding problem?

Many thanks!

G

sykes · May 22nd, 2002, 05:07 AM

Hi misterg
bit more info please, and do you want the result in hrs and mins, or just mins?

Kindest
Sykes

Ian Mac · May 22nd, 2002, 05:13 AM

Quote:

On 2002-05-22 03:54, misterg wrote:
Ok, pressed enter too quickly there, but hello anyway!

Right...my question for today is a relatively simple sounding one.

I have a formula written to represent how much time has been taken doing a task based on hours worked and work carried out.
Just trying to represent the "Amount of average time taken on a case" but...cannot find the correct format to represent the answer to the formula as minutes.

It keeps displaying the answer as a fraction of 100, not 60.

Any ideas how i can get around this rather simple sounding problem?

Many thanks!

G

see if the following helps, click on the underlined number for the formula:

Microsoft Excel - Book6

___Running: xl97 : OS = Windows (32-bit) NT 4.00

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

B9

=

A

B

C

D

1

HOURS

Formula

2

23

:alert('=A2/24')>23:00:00

Whole numbers to hours:minutes

3

23

:alert('=A3/1440')>0:23:00

Whole numbers to minutes:seconds

4

23

:alert('=A4/86400')>0:00:23

Whole number to Seconds

Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

misterg · May 22nd, 2002, 07:02 AM

Mac,

Thanks for the idea, but it doesnt really work in my formula...let me explain (maybe im just missing something here!)...

Sykes/Mac,

The way it works is that it takes the "amount of work done" from cell C7 and the "time spent working" from cell E7.

The "amount per hour" is then calculated in cell F7.

I now need cell G7 to display the average time spent on each case.

This should take into consideration the amount of hours worked and the cases per hour.

Its just getting the formula right to display this as a division of 60 mins.

Any ideas?

G

Ian Mac · May 22nd, 2002, 07:18 AM

Quote:

On 2002-05-22 06:02, misterg wrote:
Mac,

Thanks for the idea, but it doesnt really work in my formula...let me explain (maybe im just missing something here!)...

Sykes/Mac,

The way it works is that it takes the "amount of work done" from cell C7 and the "time spent working" from cell E7.

The "amount per hour" is then calculated in cell F7.

I now need cell G7 to display the average time spent on each case.

This should take into consideration the amount of hours worked and the cases per hour.

Its just getting the formula right to display this as a division of 60 mins.

Any ideas?

G

What about:

Microsoft Excel - Book6

___Running: xl97 : OS = Windows (32-bit) NT 4.00

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

A1

=

4.5

A

B

C

D

1

4.5

04:30

:alert('=A1/(B1*24)')>1

:alert('=%221:00%22/C1')>1:00:00

2

24

16:21

:alert('=A2/(B2*24)')>1.46789

:alert('=%221:00%22/C2')>0:40:52

3

1551

01:13

:alert('=A3/(B3*24)')>1274.795

:alert('=%221:00%22/C3')>0:00:03

4

70

14:56

:alert('=A4/(B4*24)')>4.6875

:alert('=%221:00%22/C4')>0:12:48

Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

?

misterg · May 24th, 2002, 07:31 AM

mac,

many thanks for the info...it certainly proved usefull, however the powers that be have changed their minds now!

Typical, thanks anyway FTI!

G

May 22nd, 2002, 05:07 AM	#3
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Hi misterg bit more info please, and do you want the result in hrs and mins, or just mins? Kindest Sykes __________________ Sykes Vista / Xl2007

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 04:51 AM	#1
misterg Board Regular Join Date: May 2002 Posts: 66	Good day to you all

May 22nd, 2002, 04:54 AM	#2
misterg Board Regular Join Date: May 2002 Posts: 66	Ok, pressed enter too quickly there, but hello anyway! Right...my question for today is a relatively simple sounding one. I have a formula written to represent how much time has been taken doing a task based on hours worked and work carried out. Just trying to represent the "Amount of average time taken on a case" but...cannot find the correct format to represent the answer to the formula as minutes. It keeps displaying the answer as a fraction of 100, not 60. Any ideas how i can get around this rather simple sounding problem? Many thanks! G

May 22nd, 2002, 07:02 AM	#5
misterg Board Regular Join Date: May 2002 Posts: 66	Mac, Thanks for the idea, but it doesnt really work in my formula...let me explain (maybe im just missing something here!)... Sykes/Mac, The way it works is that it takes the "amount of work done" from cell C7 and the "time spent working" from cell E7. The "amount per hour" is then calculated in cell F7. I now need cell G7 to display the average time spent on each case. This should take into consideration the amount of hours worked and the cases per hour. Its just getting the formula right to display this as a division of 60 mins. Any ideas? G

May 24th, 2002, 07:31 AM	#7
misterg Board Regular Join Date: May 2002 Posts: 66	mac, many thanks for the info...it certainly proved usefull, however the powers that be have changed their minds now! Typical, thanks anyway FTI! G