Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 7
There are 1 users currently browsing forums.
|
 |
11-21-2009, 03:13 PM
|
|
Registered User
|
|
Join Date: 06 Sep 2008
Location: Denver
Posts: 8
|
|
|
Time Sheet Schedule
Please Register to Remove these Ads
I know this is going to be easy for someone, but I am stumped, again.
What I need to do is calculate the hours between two clock times minus 30 minutes.
Example:
Cell A1 - 4:00am
Cell B1 - 16:30
I need Cell C1 to say 12 (hours)
Also:
Cell A2 - 17:30
Cell B2 - 02:00am
I need cell c2 to say 8 (hours)
Thanks for your help.
Last edited by davesexcel; 11-24-2009 at 01:45 AM.
|
11-21-2009, 04:16 PM
|
 |
Forum Moderator
|
|
Join Date: 19 Feb 2006
Location: Cochrane,Alberta
MS Office Version:XL 2003
Posts: 4,614
|
|
|
Re: Time Sheet Schedule
Here is one way
=IF((B1-A1)<0,((B1+12)-A1)-TIME(,30,0),(B1-A1)-TIME(,30,))
__________________
Dave
Please read the Forum Rules before posting!
If I answered your question, click the scale Icon above my location.
|
11-22-2009, 10:39 AM
|
 |
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 5,993
|
|
|
Re: Time Sheet Schedule
with start in a1 finish in b1 eg 18:00 to 01:00
=(B1-A1+(B1<A1))-1/48 gives answer as time eg 06:30 cell formatted time
or as a decimal
=(B1-A1+(B1<A1))*24-0.5 gives 6.5 cell formatted general
Last edited by martindwilson; 11-22-2009 at 10:45 AM.
|
11-22-2009, 03:18 PM
|
|
Registered User
|
|
Join Date: 06 Sep 2008
Location: Denver
Posts: 8
|
|
|
Re: Time Sheet Schedule
the second one worked perfectly Martin, Now 1 more question: Can I add an If formula to it if the answer is less than 1. Reason being if they are off and there arent any times in it it calculates -5 for the 30 min lunch.
Thanks Again!
|
11-22-2009, 03:51 PM
|
|
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 5,993
|
|
|
Re: Time Sheet Schedule
=if((B1-A1+(B1<A1))*24-0.5<1,0,(B1-A1+(B1<A1))*24-0.5)
|
11-22-2009, 10:48 PM
|
 |
Forum Guru
|
|
Join Date: 05 Mar 2008
Location: Virginia, US
MS Office Version:2007
Posts: 1,645
|
|
|
Re: Time Sheet Schedule
If you just want to avoid negative numbers, =MAX(MOD(B1-A1-1/48,1),0)*24 is a little shorter and should do the same thing.
|
11-23-2009, 11:51 PM
|
|
Registered User
|
|
Join Date: 06 Sep 2008
Location: Denver
Posts: 8
|
|
|
Re: Time Sheet Schedule
thank you all! How do I enter this as solved?
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
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
|
|
|
|
