IF Statements

JIMMARSHALL6660

New Member
Joined
Mar 13, 2002
Messages
12
I work as an administrator using excel for team registers which are then reported via pivot tables for timesheet purposes.

The problem I have is that several people work differing hours each day and on different days.

I therefore can't fix a deduction of say 1 hour as it deducts this from all hours worked irrespective of the number.

I need to be able to show if someone worked part of a day (3:30)and pay them for those hours and if more than 4 deduct 1 hour for lunch.Also required are the "H","A","S","U","OFF" AND "L"

A copy of the file and the formula used is attached.

Please help.



09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1


=IF(J9=1,H9-F9,IF(J9="OFF","OFF",IF(J9="U","U",IF(J9="A","A",IF(J9="S","S",IF(J9="H","H",IF(J9="T","T",IF(J9="L","L"))))))))
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1

Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

Additional question:
Tim/Hun/Partick or QP? :)
 
Upvote 0
On 2002-03-14 07:36, Mark O'Brien wrote:
09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1

Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

Additional question:
Tim/Hun/Partick or QP? :)

[SCHED START, ACTUAL START,SCHED FINISH,ACTUAL FINISH, TIME PRESENT]


Time value calculated on finish time less start time. and value is dependent on data entered in (present) column
 
Upvote 0
On 2002-03-14 07:36, Mark O'Brien wrote:
09:00 13:30 17:00 16:55 03:25 1
09:00 09:00 17:00 12:30 03:30 1
09:00 09:10 17:00 16:50 07:40 1
09:00 09:00 17:00 17:00 H H
09:00 09:05 17:00 16:55 07:50 1
09:00 09:00 17:00 16:50 07:50 1
09:00 09:00 17:00 17:00 08:00 1

Not really following your data, what are your columnn headers? I'm guessing there's something like, "Time In". "Time Out" etc. but I'm not quite sure.

Additional question:
Tim/Hun/Partick or QP? :)

[is that relevant: congratulations where it due]
 
Upvote 0
OK, there's probably an easier way to do this, but this is my suggestion.

Find a nice empty part of your spreadsheet and put the following data in the two columns:


1:00 0
4:00 1
H H
A A
S S
OFF OFF
L L

I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

So in J9 we're going to put a formula like this in :

=VLOOKUP(J9,$N$3:$O$9,2)

The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

This should work to give you

0 if Hours < 4
1 if hours >= 4
and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

Any problems, just repost.

"Gonnae no' dae that"
 
Upvote 0
On 2002-03-14 08:19, Anonymous wrote:
OK, there's probably an easier way to do this, but this is my suggestion.

Find a nice empty part of your spreadsheet and put the following data in the two columns:


1:00 0
4:00 1
H H
A A
S S
OFF OFF
L L

I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

So in J9 we're going to put a formula like this in :

=VLOOKUP(J9,$N$3:$O$9,2)

The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

This should work to give you

0 if Hours < 4
1 if hours >= 4
and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

Any problems, just repost.

"Gonnae no' dae that"]
["How no"]
 
Upvote 0
On 2002-03-14 08:23, JIMMARSHALL6660 wrote:
On 2002-03-14 08:19, Anonymous wrote:
OK, there's probably an easier way to do this, but this is my suggestion.

Find a nice empty part of your spreadsheet and put the following data in the two columns:


1:00 0
4:00 1
H H
A A
S S
OFF OFF
L L

I think in your example you were wanting J9 to hold one of the values(0,1,H,A,S,OFF or L)

So in J9 we're going to put a formula like this in :

=VLOOKUP(J9,$N$3:$O$9,2)

The thing to change here is the Range ($N$3:$O$9). You will change this to be the range that we put data into earlier.

This should work to give you

0 if Hours < 4
1 if hours >= 4
and return whatever text is required. I really hope this works, usually I'm just a VBA guy, but since you're from Glasgow I took pity on you. (especially as I think your leanings may be towards the Dark Side)

Any problems, just repost.

"Gonnae no' dae that"]
["How no"]
[Can't get this to work.

I'll attach a spreadsheet showing what i'm trying to do if you want to email me maybe someone outthere can help
]
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top