If help

[Peter1973]

I am trying to work out how to do this :

In cell A1 i have a time 21:00
In cell B1 I have a length of time ie 00:20 ( 20 minutes not 21 mins past midnight )
In Cell C1 I have a number ie 50.

I need a formula to look in cell A1 sunbtract B1 so in the above case would = 21:00 - 00:20 so would give 20:40. The if that time is between 19:00 and 21:00 return the value in C1 otherwise return a zero value.

Any help appreciated.

 

[Scott Huish]

With those numbers, I get 54.

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3
4						21:00		54
NP Breakdown

Excel Workbook
	A	B	C	D	E	F	G
1
2
3
4
5							1:18
NP Per Hour

Excel Workbook
	A
1	54
Sheet3

 

[Peter1973]

Think I will have to give up on this has have tried everything but I am still getting the value 0 returned.

 

[Scott Huish]

Do those times have dates with them?

 

[Peter1973]

No, they are derived from another formula but I have checked the path and all are formatted in time ie hh:mm

 

[Peter1973]

Thanks for your patience Hot Pepper I know it wasn't easy, I got the feeling something in my sheet was wrong so started again with a blank workbook and once all data in it now works great.

Only one question with the above formula is there a way to have the formula calculate multiple cells ie :
=AND('NP Breakdown'!F4-'NP Per Hour'!G5>"18:59"+0,'NP Breakdown'!F4-'NP Per Hour'!G5<"21:01"+0)*'NP Breakdown'!H4 and have it add the same formula but relplace F4 for K4 and H4 for M4 this will be repeated several times always moving 5 cells to the right, up to cc4 and ce4.
The only way I can think off is to keep repeating the formula and change the cell destinations but this would be very long.