Formulas to calculate IF cell has something entered.

[schroedc]

I need to calculate the amount of time my daughter will be in daycare which varies each week. I have to give the same sheet that I have hidden calculations on to the center. I have to have the time she arrives at 7:15 AM (Cell A1) however they don't start to bill until 7:45 AM (Cell A2) and continues until 11:30 AM (Cell B1) and need C1 to be total hours between A2 and B1

I need a formula that will calculate the hours between 7:45 AM to 11:30 ONLY if cell A1 has a time entered in it. If A1 is blank then it should calculate 0 hours.

Example (WITH something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - 7:15 am to B1 - 11:30 am C1 - 3.75 Hours

Start Billable Time
A2 7:45 am

Example (WITHOUT something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - to B1 - 11:30 am C1 - 0.00 Hours

Start Billable Time
A2 7:45 am

If it has to be in 24 hour time is there a formula that I can enter in another cell to convert 12 hour time to 24 hour time again ONLY if A1 has something entered in it?

 

[t.powley]

Hi,

Cells A1,A2,B1,C1 need to be formated to: Custom with hh:mm

in cell C1 add the following code

=IF(A1="","00:00",(IF(A1<
A2,(TEXT(B1-A2,"hh:mm")),(TEXT(B1-A1,"hh:mm")))))

For some reson it would not display the formula fully without the break in it, it should still work ok, just remove the line break if it doesnot work.

 

[AutoFilterQueen]

To calculate the total billable time, place this formula in cell C1:

=IF(A1="","0.00",(B1-A2)*24)

B1-A2 will give you the total time she spent at daycare in days, so multiplying by 24 will give you the hours. The part after the first comma is what you want the cell to show if the condition is true, and the part after the second comma is what you want the cell to show if the condition is false. Cell C1 is formatted as number with 2 decimal places. Cells A1, B1, and A2 are formatted as custom h:mm (which is what Excel automatically does when you type 7:15 into the cell).

As far as converting to 24 hour time, I would put that in a new cell (say cell D1). Format cell D1 as time, 13:30, then place this formula in cell D1:

=IF(A1="","",A1)

I would do the 24 hour conversion by formatting the cell, not within the formula.

Hope that helps!