Need formula

P

Phxlatinoboi®

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
 
T

Toppers

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".
 
S

Sandy Mann

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

[email protected]
[email protected] with @tiscali.co.uk
 
S

Sandy Mann

Sandy Mann said:
To add up all working hours for Rami use the formula:
=SUMIF(F1:F200,"Rami",I1:I200)+SUMIF(J1:J200,"Rami",M1:M3)
Click to expand...
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

[email protected]
[email protected] with @tiscali.co.uk
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top