05-10-2006, 16:32
|
#1
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Another Excel question!
Hi,
I'm creating an Excel worksheet for overtime.
Our company rules say you can't claim from 07:30 onwards and not before 18:30 for the evening.
I have three columns in the sheet - start time / end time and total hours.
What I need to do is for the user to enter start and end time for the day and for Excel to total the overtime hours BUT taking the above into account...
Anybody know what formula I shoud use for the total column?
Cheers
Lee
|
|
|
05-10-2006, 16:33
|
#2
|
Inactive
Join Date: Jan 2006
Location: Berkshire
Posts: 1,266
|
Re: Another Excel question!
Would the datediff function be any use to you for this?
|
|
|
05-10-2006, 16:35
|
#3
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Re: Another Excel question!
I've no idea what I use or how I go about it...
---------- Post added at 16:35 ---------- Previous post was at 16:34 ----------
It's more time related i think - not sure if any functions available for that...
|
|
|
05-10-2006, 16:39
|
#4
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Is the third column "total hours" or "total overtime hours"?
That is, if someone worked from 15.30 to 19.30, would you want to see a "1" in column three?
|
|
|
05-10-2006, 16:41
|
#5
|
Inactive
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
|
Re: Another Excel question!
Just use a sum.
If they worked until 19:30 then subtract 19:30 from 18:30. Leaves 1 hours overtime.
Ensure your cell formats are set to HH:MM:SS.
|
|
|
05-10-2006, 16:44
|
#6
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Re: Another Excel question!
Quote:
Originally Posted by TheNorm
Is the third column "total hours" or "total overtime hours"?
That is, if someone worked from 15.30 to 19.30, would you want to see a "1" in column three?
|
Total hours overtime....
1 = correct!
---------- Post added at 16:44 ---------- Previous post was at 16:43 ----------
Quote:
Originally Posted by gazzae
Just use a sum.
|
Right.....so what would the formula be ?
|
|
|
05-10-2006, 16:55
|
#7
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Quote:
Originally Posted by lsainsbury
Total hours overtime....
1 = correct!
Right.....so what would the formula be ?
|
How about this:
=IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30"))
She ain't pretty, but...
Start Stop Overtime
08:00 16:00 00:00
07:00 16:00 00:30
08:00 19:00 00:30
07:00 19:00 01:00
|
|
|
05-10-2006, 17:05
|
#8
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Re: Another Excel question!
Quote:
Originally Posted by TheNorm
How about this:
=IF((TIMEVALUE("7:30 AM")-A2)<0,0,TIMEVALUE("7:30 AM")-A2)+IF((B2-TIMEVALUE("18:30"))<0,0,B2-TIMEVALUE("18:30"))
She ain't pretty, but...
Start Stop Overtime
08:00 16:00 00:00
07:00 16:00 00:30
08:00 19:00 00:30
07:00 19:00 01:00
|
Hmmm...
=IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30"))
07:30 - 19:30
Result = 0.04
Should be 1
|
|
|
05-10-2006, 17:06
|
#9
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
Re: Another Excel question!
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
|
|
|
05-10-2006, 17:11
|
#10
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Quote:
Originally Posted by lsainsbury
Hmmm...
=IF((TIMEVALUE("7:30 AM")-E10)<0,0,TIMEVALUE("7:30 AM")-E10)+IF((F10-TIMEVALUE("18:30"))<0,0,F10-TIMEVALUE("18:30"))
07:30 - 19:30
Result = 0.04
Should be 1
|
Have you formatted the cells as hh:mm? 0.04 of a day is 1 hour.
Quote:
Originally Posted by Cobbydaler
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
|
Very good!
|
|
|
05-10-2006, 17:13
|
#11
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Re: Another Excel question!
Quote:
Originally Posted by Cobbydaler
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
|
Thanks - getting there ....I don't really want the other columns with number of hours - there must be a way to work it out in one formula....
|
|
|
05-10-2006, 17:15
|
#12
|
cf.mega poster
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
|
Re: Another Excel question!
Just hide columns C,D,F & G...
Protect the worksheet except for the entry cells...
|
|
|
05-10-2006, 17:16
|
#13
|
Inactive
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
|
Re: Another Excel question!
Seems like I was to slow.
This is how I would have done it.
Code:
=IF(A3>TEXT("07:30","HH:MM"),0,TEXT("07:30","HH:MM")-A3)+IF(B3>TEXT("18:30","HH:MM"),0,B3-TEXT("18:30","HH:MM"))
|
|
|
05-10-2006, 17:17
|
#14
|
Guest
Location: Near Hungerford, West Berkshire
Services: TV: Sky HD, Landline: BT,
Mobile: Orange, Internet: Quite Slow!
Posts: n/a
|
Re: Another Excel question!
Quote:
Originally Posted by TheNorm
Have you formatted the cells as hh:mm? 0.04 of a day is 1 hour.
|
Ahhhh......I thought I had - reset the formatting and BINGO!
Thanks for the work....another one solved by CF!
|
|
|
05-10-2006, 17:21
|
#15
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: Another Excel question!
Quote:
Originally Posted by gazzae
Seems like I was to slow.
This is how I would have done it.
Code:
=IF(A3>TEXT("07:30","HH:MM"),0,TEXT("07:30","HH:MM")-A3)+IF(B3>TEXT("18:30","HH:MM"),0,B3-TEXT("18:30","HH:MM"))
|
I like this! In fact, one of the things I like about Excel is that there are so many ways to solve a problem. Wonderful software - thanks Bill.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT +1. The time now is 03:23.
|