# If help

#### Peter1973

##### Well-known Member
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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### iggydarsa

##### Well-known Member
What number formatting are you using for column A and column B?

#### Peter1973

##### Well-known Member
Column A is hh:mm column B can be any formating currently it is hh:mm

#### Scott Huish

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

#### Peter1973

##### Well-known Member
This seems to return zero value for all cells even if they fall into the time bracket.

#### Peter1973

##### Well-known Member
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

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

Excel Workbook
ABCDEFGH
1
2
3
421:0050
NP Breakdown
Excel Workbook
ABCDEFG
1
2
3
4
50:20
NP Per Hour
Excel Workbook
A
150
Sheet3

#### Peter1973

##### Well-known Member
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

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

#### Peter1973

##### Well-known Member
In the 3 cells there is :

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

Thanks

Replies
8
Views
1K
Replies
4
Views
262
Replies
2
Views
164
Replies
6
Views
540
Replies
6
Views
154

1,191,281
Messages
5,985,735
Members
439,978
Latest member
Mr930R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back