I receive an excel spreadsheet from the managers with everyone's schedule. I
would like to automatically caculate how many hours each person works. Is
there a formula for this? I would like it caculate just the time.

I receive the information as follows

F1 G1
ALEX 10-7 RAMI 10-7
RAMI 11-8 CHAD 11-8
STEPHAN 10-7

I think you will need VBA for this.

Some questions:

Is data only in columns F & G?

Does 10-7 mean 10 am to 7 pm?

Will you have separate list of of all names?

I recommend you look at a better way of formatting the data from the
managers as this far from "user friendly".

"Phxlatinoboi®" wrote:

I receive an excel spreadsheet from the managers with everyone's schedule. I
would like to automatically caculate how many hours each person works. Is
there a formula for this? I would like it caculate just the time.

I receive the information as follows

F1 G1
ALEX 10-7 RAMI 10-7
RAMI 11-8 CHAD 11-8
STEPHAN 10-7

There is nothing more dangerous than a manager with a spreadsheet <g
As Toppers says the manager's data is far from user friendly.

If you don't want to get into VBA and the data from the manager is
consistant like "Name <space start time (whole) number dash end time
number"
then it is still going to be a lot of work but try, (on a copy of the
manager's data)

Insert three new columns after Column F and another three after Column G
Highlight the data in Column F and then select Data Text to Columns
Delimited Next
Tick "Space" and "Other" and in the box to the left of Other enter a dish
(-)
then select Next Finish

This will place Alex's 10 start time in the first column (G) and the 7 end
time
in the second column (H)
Do the same for the data that was in Column G and is now in Column J

In an empty cell enter 12 and copy it. Highlight all the data in the new
columns representing the end times (H & L) and the Paste Speecial Add
this will make Alex's end time 19

Now in an empty cell enter 24 and copy that. Highlight all the start and
end times and Paste Special Divide then will create decimal numbers under
1. ie Alex time will be 0.416667 and 0.7916667 respectively

Format all the times (the decimal numbers) as time 13:30 and in the third
inserted column (which will be Column I), enter the formula:
=H1-G1 and copy down

Do the same for the end times.

To add up all working hours for Rami use the formula:
=SUMIF(F1:F200,"Rami",I1:I200)+SUMIF(J1:J200,"Rami ",M1:M3)

It may be better to enter all the names in a table and then reference them
instead of hard coding them into the formula.

I told you it was a lot of work.

Post back if you need more help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Phxlatinoboi®" wrote in message
...
I receive an excel spreadsheet from the managers with everyone's schedule.
I
would like to automatically caculate how many hours each person works. Is
there a formula for this? I would like it caculate just the time.

I receive the information as follows

F1 G1
ALEX 10-7 RAMI 10-7
RAMI 11-8 CHAD 11-8
STEPHAN 10-7

"Sandy Mann" wrote in message
...
To add up all working hours for Rami use the formula:
=SUMIF(F1:F200,"Rami",I1:I200)+SUMIF(J1:J200,"Rami ",M1:M3)

I omitted to add that if you do follow this method then custom format the
cell as [h]:mm


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

On Fri, 20 Apr 2007 12:00:03 -0700, Phxlatinoboi®
wrote:

I receive an excel spreadsheet from the managers with everyone's schedule. I
would like to automatically caculate how many hours each person works. Is
there a formula for this? I would like it caculate just the time.

I receive the information as follows

F1 G1
ALEX 10-7 RAMI 10-7
RAMI 11-8 CHAD 11-8
STEPHAN 10-7








This is pretty complicated. As Sandy intimated, it would be far, far better to
design a proper data input sheet.

If that is absolutely not possible, the following might work.

The following assumptions are made:

1. The format is as follows:
<NAME<space<StartTime<hyphen<EndTime

2. There are no <space's in <NAME.

Some format changes may be allowable, but we would have to know what they are.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

List the names in a column someplace: e.g. A1:A4

ALEX
RAMI
CHAD
STEPHAN


Name the range where the information is stored "tbl"

Insert/Name/Define


**Array enter** the following formula in B1, and copy/drag down to B4. To
**array-enter** a formula, after typing or pasting the formula into the cell,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.

B1:
=SUM(IF(ISERR(-REGEX.MID(tbl,"(?<="&$A1&"\s)"&"\d+(?=-)")),0,
-REGEX.MID(tbl,"(?<="&$A1&"\s)"&"\d+(?=-)")+
REGEX.SUBSTITUTE(REGEX.MID(tbl,"(?<="&$A1&"\s)"&". *"),"(\d+-)")+
12*(---REGEX.SUBSTITUTE(REGEX.MID(tbl,"(?<="&$A1&"\s)"&". *"),"(\d+-)")
<--REGEX.MID(tbl,"(?<="&$A1&"\s)"&"\d+(?=-)"))))


--ron