TIME CALCULATION

[FEVOOO]

I HAVE EXCEL SHEET TO CALCULATE TIME FOR WORKING HOURS, USEING FROM TO AND TOTAL TIME AS FOLLOWING:
FROM (A2), TO (B2) , TOTAL (C2)
SHIFT HOURS FROM 06:00AM TO NEXT DAY 06:00AM AS 24HRS FORMAT CELL
THE PROBLEM IS AFTER PASSING 24:00 MIDNIGHT (FROM 01:00AM TO 06:00AM) TOTAL IS NOT SHOWING BECAUSE ITS LESS THAN 24.
PLEASE HELP.

 

[Kevin@Radstock]

Hi

Start time; A2
End time: B2
You can use the MOD function: =MOD(B2-A2,1)
or
=IF(B2<A2,B2+1,B2)-A2

Format cells as [h]:mm

Kevin

 

[joeu2004]

I HAVE EXCEL SHEET TO CALCULATE TIME FOR WORKING HOURS [....]
FROM (A2), TO (B2) , TOTAL (C2)
SHIFT HOURS FROM 06:00AM TO NEXT DAY 06:00AM AS 24HRS
FORMAT CELL THE PROBLEM IS AFTER PASSING 24:00 MIDNIGHT
(FROM 01:00AM TO 06:00AM) TOTAL IS NOT SHOWING BECAUSE
ITS LESS THAN 24.

First, the format of the cell does not matter.

Second, to avoid the problem with computing negative time, use the following
formula:

=B2-A2+(A2>B2)

formatted as Time or whatever you want.

 

[Spencer101]

I HAVE EXCEL SHEET TO CALCULATE TIME FOR WORKING HOURS, USEING FROM T
AND TOTAL TIME AS FOLLOWING:
FROM (A2), TO (B2) , TOTAL (C2)
SHIFT HOURS FROM 06:00AM TO NEXT DAY 06:00AM AS 24HRS FORMAT CELL
THE PROBLEM IS AFTER PASSING 24:00 MIDNIGHT (FROM 01:00AM TO 06:00AM
TOTAL IS NOT SHOWING BECAUSE ITS LESS THAN 24.
PLEASE HELP.

Try this in C2:

*=IF(B2<A2,(B2+1)-A2,B2-A2)

 

[Claus Busch]

Hi,

or try:
=MOD(B2-A2,1)


Regards
Claus Busch

 

[FEVOOO]

I HAVE EXCEL SHEET TO CALCULATE TIME FOR WORKING HOURS
[....]

FROM (A2), TO (B2) , TOTAL (C2)

SHIFT HOURS FROM 06:00AM TO NEXT DAY 06:00AM AS 24HRS

FORMAT CELL THE PROBLEM IS AFTER PASSING 24:00 MIDNIGHT

(FROM 01:00AM TO 06:00AM) TOTAL IS NOT SHOWING BECAUSE

ITS LESS THAN 24.

First, the format of the cell does not matter.



Second, to avoid the problem with computing negative time, use the following

formula:



=B2-A2+(A2>B2)



formatted as Time or whatever you want.

Thank you so muuuuuch, its working perfectly

 

[FEVOOO]

Try this in C2:



*=IF(B2<A2,(B2+1)-A2,B2-A2)*

yeeeees, I was tring all the night fo IF formula appreciate

 

[FEVOOO]

Hi,



or try:

=MOD(B2-A2,1)





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

thnaks alot for your efforts

 

[Kevin@Radstock]

@ fevooo

you must be "blind" or just "stupid", look at post 2!