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

tazmtiger

Board Regular
Joined
Jul 7, 2005
Messages
194
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!
 

Some videos you may like

Excel Facts

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jul 7, 2005
Messages
194
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jul 7, 2005
Messages
194
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jul 7, 2005
Messages
194
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!
 

tazmtiger

Board Regular
Joined
Jul 7, 2005
Messages
194
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,519
Messages
5,469,111
Members
406,637
Latest member
Mbsmbs

This Week's Hot Topics

Top