Time Sheet Schedule

**manickmj1** · 11-21-2009, 03:13 PM

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.

**davesexcel** · 11-21-2009, 04:16 PM

Here is one way
=IF((B1-A1)<0,((B1+12)-A1)-TIME(,30,0),(B1-A1)-TIME(,30,))

**martindwilson** · 11-22-2009, 10:39 AM

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

**manickmj1** · 11-22-2009, 03:18 PM

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!

**martindwilson** · 11-22-2009, 03:51 PM

=if((B1-A1+(B1<A1))*24-0.5<1,0,(B1-A1+(B1<A1))*24-0.5)

**darkyam** · 11-22-2009, 10:48 PM

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.

**manickmj1** · 11-23-2009, 11:51 PM

thank you all! How do I enter this as solved?

Thread: Time Sheet Schedule

Thread Tools

Display

Time Sheet Schedule

Re: Time Sheet Schedule

Re: Time Sheet Schedule

Re: Time Sheet Schedule

Re: Time Sheet Schedule

Re: Time Sheet Schedule

Re: Time Sheet Schedule

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions