calculate time elapsed inbetween days

[endep]

Greetings,

First time on the forum. I'm building a schedule in excel and am having trouble with the calculation of total hours when lapsing a day. For example;Monday they clock in at 5pm and work till 1am. I have found a couple of ways to get the desired out come from this on it's own. I just add the value of a day if the out time is less than the in time, or if the out time minus the in time is less than 0. However, I need to add this calculation into an equation that is already calculating time elapsed within a single day,turning that into a decimal number and then checking if the total time elapsed is less than 6 hours, if false it subtracts a thirty minute break. Here is the current equation I'm using, I'm sure that there is an if(or, or if(and I could use to make it work when the time lapses 12am. Any help wouldbe most appreciated.

=IF(((F7-INT(D7))*24)-(D7-INT(F7))*24<6,(((F7-INT(D7))*24)-(D7-INT(F7))*24),((F7-INT(D7))*24)-(D7-INT(F7))*24-0.5)

D7= Time in. F7= Time out.

thanks

 

[Claus Busch]

Hi,

Am Fri, 24 Aug 2012 05:26:12 -0700 (PDT) schrieb endep:

First time on the forum. I'm building a schedule in excel and am having trouble with the calculation of total hours when lapsing a day. For example; Monday they clock in at 5pm and work till 1am. I have found a couple of ways to get the desired out come from this on it's own. I just add the value of a day if the out time is less than the in time, or if the out time minus the in time is less than 0. However, I need to add this calculation into an equation that is already calculating time elapsed within a single day, turning that into a decimal number and then checking if the total time elapsed is less than 6 hours, if false it subtracts a thirty minute break. Here is the current equation I'm using, I'm sure that there is an if(or, or if(and I could use to make it work when the time lapses 12am. Any help would be most appreciated.

=IF(((F7-INT(D7))*24)-(D7-INT(F7))*24<6,(((F7-INT(D7))*24)-(D7-INT(F7))*24),((F7-INT(D7))*24)-(D7-INT(F7))*24-0.5)

D7= Time in. F7= Time out.

try:
=IF(MOD(F7-D7,1)<0.25,MOD(F7-D7,1)*24,MOD(F7-D7,1)*24-0.5)


Regards
Claus Busch

 

[endep]

Hi,



Am Fri, 24 Aug 2012 05:26:12 -0700 (PDT) schrieb endep:






try:

=IF(MOD(F7-D7,1)<0.25,MOD(F7-D7,1)*24,MOD(F7-D7,1)*24-0.5)





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Perfect! Thanks so much! Way less complicated than what I had going on too. I can mostly read the equation now that I kinda understand MOD. Thanksagain!

 

[Claus Busch]

Hi again,

Am Fri, 24 Aug 2012 06:11:48 -0700 (PDT) schrieb endep:

Perfect! Thanks so much! Way less complicated than what I had going on too. I can mostly read the equation now that I kinda understand MOD. Thanks again!

you can shorten the formula:
=MOD(F7-D7,1)*24-(MOD(F7-D7,1)>0.25)*0.5

Regards
Claus Busch