Need help getting rid off #VALUE! error in time calculations when using text on cells.

[tazmtiger]

Hi

I am working on a work schedule with time calculations and I am using text in some cells, but this creates a #VALUE! error in the summary line. Can some one please help me find a solution using the two folowing formula lines:

AY =((D12-INT(D12))*24)-((C12-INT(C12))*24)
BA =IF(AY24<0,24+AY24,AY24)

Thank you!

 

[Jonmo1]

Try

=IF(OR(C12="Close",D12="Close"),"some specified value",IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0))

 

[tazmtiger]

Try

=IF(OR(C12="Close",D12="Close"),"some specified value",IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0))

I am sorry! I don't think I explained myself correctly... The above example you provided, only returns a true / false statement.

I needed it to return a numeric value. Lets say for example: 8

 

[Jonmo1]

OK, list some example values of C12 and D12 with the expected results of each..

C12 Off And D12 = Time, I want ??
C12 =Time and D12 = Off, I want ??
C12 = "Close and D12 = Time, I want ??
etc...

List all possible combinations of C12/D12 and your expected results.

 

[tazmtiger]

OK, list some example values of C12 and D12 with the expected results of each..

C12 Off And D12 = Time, I want ??
C12 =Time and D12 = Off, I want ??
C12 = "Close and D12 = Time, I want ??
etc...

List all possible combinations of C12/D12 and your expected results.

C12 = 4:00PM D12 = CLOSE
C12 = 4:00 PM and D12 OFF = 10:30 PM

Does that make any sense to you?

I hope it does?

I don't think there is a solution to that?

Thanks!

 

[Jonmo1]

No not really...

If either a timevalue | Close | Off can appear in Either C12 or D12, then there are 9 possible combinations of those...

Please fill in the blanks..

C12	D12	Expected Result
TimeValue	TimeValue
TimeValue	Off
TimeValue	Close
Off	TimeValue
Off	Off
Off	Close
Close	TimeValue
Close	Off
Close	Close

<tbody>
</tbody>

 

[tazmtiger]

I could not re-create your table above, so I am using plain text to answer all above requests.


C12 D12 Expected Results

Any Time Any Time Diference from times at left

None (blank) OFF 0

4:00 PM Close 6.5

OFF No Value (0) 0

OFF OFF 0

OFF CLOSE <-Not Used with off 0

CLOSE<-Not Used here None 0

CLOSE<-Not Used here None 0

CLOSE<-Not Used here None 0

If it cannot be done, I would understand. And I thank you in advance for all your help.

Thank you!

 

[Jonmo1]

Try

=IF(COUNTIF(C12:D12,"OFF"),0,IF(COUNTIF(C12:D12,"Close"),6.5,IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0)))

 

[tazmtiger]

Try

=IF(COUNTIF(C12:D12,"OFF"),0,IF(COUNTIF(C12:D12,"Close"),6.5,IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0)))

(((( Awesome! You are Awesome!!!)))

Great job!

I hate to push my luck; but I have one final request for you Using the same solution above,

I need to create 3 different time values: Example: 6Close = 6.5 7Close = 7 8Close = 8

Is it possible to add these three options to that line?

Thank you so much for all your help!

 

[Jonmo1]

Which cell does the *close value actually appear? C12 or D12, or could it be in either?

 

[tazmtiger]

Which cell does the *close value actually appear? C12 or D12, or could it be in either?

Could be in either!