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.

 

[iggydarsa]

What number formatting are you using for column A and column B?

 

[Peter1973]

Column A is hh:mm column B can be any formating currently it is hh:mm

 

[Scott Huish]

=and(a1-b1>"18:59"+0,a1-b1<"21:01"+0)*c1

 

[Peter1973]

This seems to return zero value for all cells even if they fall into the time bracket.

 

[Peter1973]

I have modified the formula slightly to fit my range to :

=AND('NP Breakdown'!F4-'NP Per Hour'!G5>"18:59"+0,'NP Breakdown'!F4-'NP Per Hour'!G5<"21:01"+0)*'NP Breakdown'!H4
G5 & F4 are in hh:mm format and H4 is in number format.

Yet even when they are within the time bracket it is just returning a zero value any ideas.

 

[Scott Huish]

Your sample returned 50 for me, are you sure you have your cell references correct?

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

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

Excel Workbook
	A
1	50
Sheet3

 

[Peter1973]

I think I must have something wrong as even when I do a simple calculation it wont work, I have done :

='NP Breakdown'!F4-'NP Per Hour'!G5
This returns ################# value i'm sure this is how it is formated but I have tried everything it is currently se tus as F4 = hh:mm G5 = hh:mm

 

[Scott Huish]

That's probably because you have negative time, but that's not the formula I posted. What values are in the three cells?

 

[Peter1973]

In the 3 cells there is :

F4 = 21:00 G5 = 01:18 and H4 = 54.

Thanks