Prev: Excel error: "The path you entered, ---------- , is too long. "
Next: Get Data from Quickbooks
From: Kimti on 12 Apr 2010 20:39
I would like to set up the formula based on three cells. I have data as
follows:

A B
10:34 Yes

In column C I would like to set up formula to get the differance between
column A1 and 11:00 based on the column B1. If B1 has yes then the
calculation has to happen, if B1 has No then the value in C1 should be equal
to A1. I have about 800 rows to calculate. Once I set the formula then I can
copy and paste.

Thank you in advance for your help.
Kimti
From: FSt1 on 12 Apr 2010 21:21
hi
you didn't specify if we are talking am or pm so i assume am
=if(B2="Yes",Time(11,00,00)-A2,A2)

excel formulas seldom likes formated dates and time within a formula so you
either have to put the time in another cell (example D2) and have the formula
like this..
=if(B2="yes", D2-A2,A2)
or use the time formula within the if formula as i did above.
the time formula uses military time ie hour = 0-23 with 0 being midnight and
23 being 11pm.

regards
FSt1

"Kimti" wrote:

> I would like to set up the formula based on three cells. I have data as
> follows:
>
> A B
> 10:34 Yes
>
> In column C I would like to set up formula to get the differance between
> column A1 and 11:00 based on the column B1. If B1 has yes then the
> calculation has to happen, if B1 has No then the value in C1 should be equal
> to A1. I have about 800 rows to calculate. Once I set the formula then I can
> copy and paste.
>
> Thank you in advance for your help.
> Kimti
 | 
Pages: 1
Prev: Excel error: "The path you entered, ---------- , is too long. "
Next: Get Data from Quickbooks