help with time formula

[cjbarron5]

I have this formula =TEXT(D2-C2,"h:mm") but when d2 and c2 are empty it
displays 0:00 I think I can use =if(isna.........but I cant get the format
right
any ideas?
thanks

 

[BobT]

Your best bet would be something like:

=IF(B7-B8=0,"Hello",TEXT(B7-B8,"h:mm"))

Of course, replace Hello with any text or value you want.

 

[cjbarron5]

works great when there is no times in the cells but it will not compute time
then entered in the cells

 

[ward376]

If you use a text function on times, they're converted to text, which
you can't do math on. Try not using TEXT and just subtract one cell's
value from the other, then formatting the output as a time. Be aware
that Excel won't display negative time values.

You probably don't want the same result if the times are the same vs
when both values are missing either.

=IF(LEN(C2&D2)=0,"Both times are missing",IF(D2="","The second time is
missing",IF(C2="","The first time is missing",IF(C2>D2,C2-D2,"d2 is
equal to or later than c2"))))

Cliff Edwards

 

[ward376]

Times are just formatted numbers on the right side of the decimal.

 

[cjbarron5]

ill give it a shot and see ......the text was given by a search on office
online help in excel just so you know
ill let you know if it works out
im trying to figure time spent on a "project" by entering begining time and
end time

 

[ward376]

Working with times and dates in Excel can seem complex, but what helps
me is thinking of them as numbers; dates to the left of the decimal
and times to the right. I just do whatever math I need to do and
format it when it's done.

Cliff Edwards