Time Question

[deerxing]

Can someone tell me why this formula does not work?

=MAX(MIN(0:00,0:00)-MAX(23:00,21:45),0), the answer I am seeking is 1 hour (from 23:00 to 0:00). This formula works great for all time slots except those ending at midnight (0:00).



Thanks

 

[jasonb75]

Bad logical execution is one reason, to break it down a little

=MAX(MIN(0:00,0:00)-MAX(23:00,21:45),0) equates to

=MAX(0:00-23:00,0)

0:00-23:00 will give a negative value, meaning 0 is the max value, and final result.

Why do you have 4 times in the formula? A non-working formula is meaningless without a description of what you're trying to do.

 

[T. Valko]

Can someone tell me why this formula does not work?

=MAX(MIN(0:00,0:00)-MAX(23:00,21:45),0), the answer I am seeking is 1 hour (from 23:00 to 0:00). This formula works great for all time slots except those ending at midnight (0:00).



Thanks

Try this...

Book1
	A	B
1	11:00 PM	12:00 AM
2	9:45 PM	12:00 AM
Sheet3

=MAX(0,MIN(B1,B2)-MAX(A1,A2)+1)

Format as h:mm

 

[deerxing]

A B
1 21:45 0:00

2 23:00 0:00


Refering to cells 1A & 1B (employee in & out punch for a shift) I need a formula that will give me the amount of time in the shift that fell between cells 2A & 2B.

 

[ashoengot]

=IF(B3>=A3,B3-A3,(24-(A3-B3)))


Try this formula to get the time duration
NOTE: we have the same table...

 

[deerxing]

Did not render the result I am looking for.

I have a shift start time noted in cell 1A and a shift end time noted in cell 1B and I want to use a formula to apply to all shifts worked to determine how many of the hours in each shift fall between the values in cell 1A & 1B (23:00 (11pm) and 0:00 (12am)), any other ideas?

 

[barry houdini]

Try this version

=MAX(MIN(B2+(B2< A2),$B$1+($B$1< $A$1))-MAX(A2,$B$1),0)

 

[Xtrimmer]

I use this on my spreadsheet and it works for time ending that is less than time starting.

= ((B1 < A1) + B1 - A1) + ((B2 < A2) + B2 - A2)

 

[VoG]

I use this on my spreadsheet and it works for time ending that is less than time starting.

=(B1<a1)+b1-a1><a1)+b1-a1>< p>

Post your formula then insert a space after any < signs before clicking the Submit Reply button. The board software interprets the < as an HTML tag.
</a1)+b1-a1></a1)+b1-a1>

 

[barry houdini]

Sorry, one of the cell references was wrong in my suggestion, should be

=MAX(MIN(B2+(B2< A2),$B$1+($B$1< $A$1))-MAX(A2,$A$1),0)