I am new to this forum but desperately need help.

I am creating a time sheet, and cannot find a solution to calculate the duration of time worked on a night shift.

The night shift rate starts from 11:00 PM until 7:00 AM and is written in the format given.

Therefore any period worked between these hours is classed as a night shift rate. Excel I believe does not recognise AM or PM therefore I have problems.

Finally in addition I need to multiply the answer by the hourly rate.

Please can anybody assist, Thank you. ]]>

Trying to get a formula to return the following:

Column A: Title

Column B: Salary 1

Column C: Salary 2

Column D: Salary 3

Column E: Salary 4

Based of of this matrix if Employee n's salary falls in between one of the 4 criteria, then I would like the higher value returned

Example:

A: Manager

B: 1000

C: 5000

D: 10000

E: 15000

My lookup index: Title - Manager / Salary - 8000.

I want this to give me the value in column D. If the salary was increased to 11,000 then column E.

So far I am stuck at:

=sumproduct(--('TitleInput'=$A:$A),--($B:$B<'SalaryInput'),--($C:$C<'SalaryInput'),--($D:$D<'SalaryInput'),($E:$E))

Where I only get a returned value if the Salary input is larger than D and Smaller than E. If I make the value D or smaller I get 0.

Thanks!

]]>

I have multiple clients that I keep time sheets for using Google Sheets. I am contracted for a certain number of hours per fortnight. However, during busy seasons I need to get the job done but keep a log of extra hours and then invoice for those hours later on and take time off in Lieu.

Each fortnight is set up as a seperate sheet with dates and hours logged as well as extra hours

I need to find a way to keep a running tally across all the sheets. A floating cell or two or frozen cell would be good, I just can't seem to find how to do it.

Is this possible? How???

Thanks in advance

]]>

Is there any way cells get automatically populated with certain value when cleared or made empty?

Example,

Value to put on empty cells: 1234

A1 = 11

A2 = empty

A3 = 22

If the automated fill works the cells should have:

A1 = 11

A2 = 1234

A3 = 22

If I empty A1 then 1234 should be put automatically in it.

If I fill A2 with 33 then A2 should contain 33 until I clear it, then it should have 1234 again.

Appreciate any help on this question.

Ronald.

]]>

If I want to do a linear regression with 1/x weighting, how do I calculate the weights for the X data and Y data?

Thanks,

Baylye

]]>

=IF(AE16="v",IF(AG16="w",-5.5,-0.5),IF(AE16="ns",-0.5,IF(OR(AE16="x",

AE16="$",AE16="hhv",AE16="cc"),-2.45,IF(AE16="app",0,""))))

....with this VLOOKUP:

=IF(AE16="?","",VLOOKUP(AE16,defaults!C:E,IF(AG16= "w",3,2)))

The lookup table is:

Type Fee WC

0 $0.00 $0.00

$ -$2.45 -$2.45

app $0.00 $0.00

cc -$2.45 -$2.45

hhv -$2.45 -$2.45

ns -$0.50 -$0.50

v -$0.50 -$5.50

x -$2.45 -$2.45

The problems are two-fold:

1) The VLOOKUP doesn't seem to be able to handle a literal "?", thus

necessitating the 1st IF(). Is there any way to include a "Type" of "?" in my

table?

2) The only difference between the table's "Fee" and "WC" columns is the "v"

value. This is handled by the 2nd IF(). Is there a better way to deal with

this?

--

There exists a limit to the force even the most powerful

may apply without destroying themselves.

]]>