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

#### tazmtiger

##### Board Regular
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!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

##### Well-known Member
Did you try the value() formula?

#### Jonmo1

##### MrExcel MVP
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

##### Board Regular
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

Last edited:

#### Jonmo1

##### MrExcel MVP
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

##### Board Regular
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.

Last edited:

#### Jonmo1

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
Glad to help, thanks for the feedback.

#### tazmtiger

##### Board Regular
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!