Home News Forum Articles
  Welcome back Join CF
You are here You are here: Home | Forum | Microsoft to end support for Windows Phone

You are currently viewing our boards as a guest which gives you limited access to view most of the discussions, articles and other free features. By joining our Virgin Media community you will have full access to all discussions, be able to view and post threads, communicate privately with other members (PM), respond to polls, upload your own images/photos, and access many other special features. Registration is fast, simple and absolutely free so please join our community today.


Welcome to Cable Forum
Go Back   Cable Forum > Computers & IT > General IT Discussion

Another Excel question!
Reply
 
Thread Tools
Old 05-10-2006, 17:32   #1
LSainsbury
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
  Reply With Quote
Advertisement
Old 05-10-2006, 17:33   #2
brundles
Inactive
 
Join Date: Jan 2006
Location: Berkshire
Posts: 1,266
brundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of societybrundles is a pillar of society
Re: Another Excel question!

Would the datediff function be any use to you for this?
brundles is offline   Reply With Quote
Old 05-10-2006, 17:35   #3
LSainsbury
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...
  Reply With Quote
Old 05-10-2006, 17:39   #4
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
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?
TheNorm is offline   Reply With Quote
Old 05-10-2006, 17:41   #5
gazzae
Inactive
 
gazzae's Avatar
 
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
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.
gazzae is offline   Reply With Quote
Old 05-10-2006, 17:44   #6
LSainsbury
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 View Post
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 View Post
Just use a sum.
Right.....so what would the formula be ?
  Reply With Quote
Old 05-10-2006, 17:55   #7
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
Re: Another Excel question!

Quote:
Originally Posted by lsainsbury View Post
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
TheNorm is offline   Reply With Quote
Old 05-10-2006, 18:05   #8
LSainsbury
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 View Post
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
  Reply With Quote
Old 05-10-2006, 18:06   #9
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
Cobbydaler is offline   Reply With Quote
Old 05-10-2006, 18:11   #10
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
Re: Another Excel question!

Quote:
Originally Posted by lsainsbury View Post
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 View Post
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
Very good!
TheNorm is offline   Reply With Quote
Old 05-10-2006, 18:13   #11
LSainsbury
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 View Post
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....

  Reply With Quote
Old 05-10-2006, 18:15   #12
Cobbydaler
cf.mega poster
 
Cobbydaler's Avatar
 
Join Date: Apr 2006
Location: Manchester, UK
Services: ClearFibre Internet, Vodafone mobile Google Pixel 4
Posts: 9,699
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Cobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered starsCobbydaler is seeing silvered stars
Re: Another Excel question!

Just hide columns C,D,F & G...

Protect the worksheet except for the entry cells...
Cobbydaler is offline   Reply With Quote
Old 05-10-2006, 18:16   #13
gazzae
Inactive
 
gazzae's Avatar
 
Join Date: Jun 2003
Location: Belfast
Age: 44
Posts: 4,594
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
gazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronzegazzae is cast in bronze
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"))
gazzae is offline   Reply With Quote
Old 05-10-2006, 18:17   #14
LSainsbury
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 View Post
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!

  Reply With Quote
Old 05-10-2006, 18:21   #15
TheNorm
Inactive
 
TheNorm's Avatar
 
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
TheNorm has a nice shiny star
TheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny starTheNorm has a nice shiny star
Re: Another Excel question!

Quote:
Originally Posted by gazzae View Post
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.
TheNorm is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +1. The time now is 12:20.


Server: osmium.zmnt.uk
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.