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!

 

[sheetspread]

Did you try the value() formula?

 

[Jonmo1]

Can you be more specific?

Which Cell(s) contain text?
What exactly IS(Are) that text string?
What is the normal NON Text value ?

 

[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 following formula lines:

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

Thank you!

I am using the following text in some cells: "OFF"

Normal Non text values are times. Example: (C12) 10:00 AM (D12) 4:00 PM

 

[Jonmo1]

ok, so what result are you expecting when OFF is in one of the cells?

Try something like

AY =IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),"OFF")

 

[tazmtiger]

ok, so what result are you expecting when OFF is in one of the cells?

Try something like

AY =IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),"OFF")

I DID NOT WORK, Because it needs to return a Zero on (AY) Cell. So. It can summarize correctly where time is added up in another line.

Still has a #VALUE! error with your formula string.

 

[Jonmo1]

Because it needs to return a Zero on (AY) Cell.

You didn't say it needs to return a 0, that's why I asked what result you expected...

Try
AY =IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0)

 

[tazmtiger]

You didn't say it needs to return a 0, that's why I asked what result you expected...

Try
AY =IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0)

THANK YOU SO MUCH!!!!

That did the job.

Have a wonderful day!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[tazmtiger]

You didn't say it needs to return a 0, that's why I asked what result you expected...

Try
AY =IF(COUNT(C12:D12)=2,((D12-INT(D12))*24)-((C12-INT(C12))*24),0)

Hi Jonmo1,

I was wondering if there is a way to use the above solution you gave me, WHICH WORKED perfectly! To make it do the following request?:

I have to schedule a few people to close, which never has an specific time. But I was wondering if it would be possible to use your formula string here to provide "Lets say" a value to the word "CLOSE"

It still needs to return a zero value if the word "CLOSE" is not used. But if I use the word "CLOSE" can I make the word CLOSE return any specified value?

I am not sure if this can be done?

I would apreciate your input. Thank you!