Cable Forum
Page 1 of 3 1 23
Show 100 post(s) from this thread on one page

Cable Forum (https://www.cableforum.uk/board/index.php)
-   General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)
-   -   Another Excel question! (https://www.cableforum.uk/board/showthread.php?t=33601687)

LSainsbury 05-10-2006 16:32
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

brundles 05-10-2006 16:33
Re: Another Excel question!
 
Would the datediff function be any use to you for this?

LSainsbury 05-10-2006 16:35
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...

TheNorm 05-10-2006 16:39
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?

gazzae 05-10-2006 16:41
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.

LSainsbury 05-10-2006 16:44
Re: Another Excel question!
 
Quote:
Originally Posted by TheNorm (Post 34130291)
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 (Post 34130293)
Just use a sum.
Right.....so what would the formula be ?

TheNorm 05-10-2006 16:55
Re: Another Excel question!
 
Quote:
Originally Posted by lsainsbury (Post 34130296)
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

LSainsbury 05-10-2006 17:05
Re: Another Excel question!
 
Quote:
Originally Posted by TheNorm (Post 34130304)
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

Cobbydaler 05-10-2006 17:06
Re: Another Excel question!
 
1 Attachment(s)
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...

TheNorm 05-10-2006 17:11
Re: Another Excel question!
 
Quote:
Originally Posted by lsainsbury (Post 34130315)
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 (Post 34130316)
Attached any good? Gives you the facility to change the start & end qualifying times if you need to...
Very good!

LSainsbury 05-10-2006 17:13
Re: Another Excel question!
 
Quote:
Originally Posted by Cobbydaler (Post 34130316)
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....

:tu:

Cobbydaler 05-10-2006 17:15
Re: Another Excel question!
 
Just hide columns C,D,F & G...

Protect the worksheet except for the entry cells...

gazzae 05-10-2006 17:16
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"))

LSainsbury 05-10-2006 17:17
Re: Another Excel question!
 
Quote:
Originally Posted by TheNorm (Post 34130321)
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!

:tu::tu: :)

TheNorm 05-10-2006 17:21
Re: Another Excel question!
 
Quote:
Originally Posted by gazzae (Post 34130324)
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.


All times are GMT +1. The time now is 22:52.
Page 1 of 3 1 23
Show 100 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.