|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Mar 14th, 2002, 08:23 AM
|
#1 |
|
New Member
Join Date: Mar 2002
Location: Glasgow
Posts: 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")))))))) |
|
|
|
Mar 14th, 2002, 08:36 AM
|
#2 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Columbus, OH, USA
Posts: 3,519
|
Quote:
Additional question: Tim/Hun/Partick or QP?  |
|
|
|
|
|
Mar 14th, 2002, 08:41 AM
|
#3 | |
|
New Member
Join Date: Mar 2002
Location: Glasgow
Posts: 12
|
[quote]
On 2002-03-14 07:36, Mark O'Brien wrote: Quote:
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 |
|
|
|
|
|
Mar 14th, 2002, 08:42 AM
|
#4 | |
|
New Member
Join Date: Mar 2002
Location: Glasgow
Posts: 12
|
[quote]
On 2002-03-14 07:36, Mark O'Brien wrote: Quote:
Additional question: Tim/Hun/Partick or QP? [is that relevant: congratulations where it due] |
|
|
|
|
|
Mar 14th, 2002, 09:19 AM
|
#5 |
|
Guest
Posts: n/a
|
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: [table] 1:00 0 4:00 1 H H A A S S OFF OFF L L [/table] 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" |
|
|
|
Mar 14th, 2002, 09:20 AM
|
#6 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Columbus, OH, USA
Posts: 3,519
|
That anonymous posting was me.
Cheers |
|
|
|
|
Mar 14th, 2002, 09:23 AM
|
#7 |
|
New Member
Join Date: Mar 2002
Location: Glasgow
Posts: 12
|
[quote]
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: [table] 1:00 0 4:00 1 H H A A S S OFF OFF L L [/table] 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"] |
|
|
|
|
Mar 14th, 2002, 11:55 AM
|
#8 |
|
New Member
Join Date: Mar 2002
Location: Glasgow
Posts: 12
|
[quote]
On 2002-03-14 08:23, JIMMARSHALL6660 wrote: [quote] 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: [table] 1:00 0 4:00 1 H H A A S S OFF OFF L L [/table] 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 ] |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
