Excel CountBlank 2 Cells at once 2

[djieon]

Hi All,

I have an excel sheet where I want to have a time from cell and a time too cell and want to automatically work out the duration.

IE

Time From 08:20
Time To 09:50

Duration 01:30:00

I have worked out how to do the duration using the formula:-

TEXT(F5-C5,"h:mm:ss")

However I want the duration cell to be blank until both the Time From and Time To are filled in...

I have tried a formula along the lines of

if (isblank (C5+F5) but this is not working and I am struggling, is there some formula i can write to check if both cells are not blank..

Thanks.

 

[Deniall]

IF(AND(ISBLANK(C5),ISBLANK(F5)), whentrue, whenfalse)

 

[SkipVought]

Hi,

TEXT() returns a TEXT value, not a NUMERIC value. TEXT values cannot be used in mathematical calculations, only NUMERIC!

[pre]
C. D. E. F. G
Total Duration : 2:14:50

8:20 9:50 1:30:00
8:20
9:50
8:55:30 9:40:20 0:44:50
[/pre]

My formula in column G
[tt]
G5: =IF(AND(NOT(ISBLANK(C5)),NOT(ISBLANK(F5))),F5-C5,"")
[/tt]
...copied thru G8...
...AND column G has a Number Format of [tt][h]:mm:ss[/tt] in order to display hours greater than or equal to 24.

BTW,
[tt]
G3: =SUM(G5:G8)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[RobertPhillips]

=IF(OR(ISBLANK(C5),ISBLANK(F5)),"",F5-C5) and format as [h]:mm:ss

Can the end time ever be in the next day?

 

[SkipVought]

@RobertPhillips,

Welcome to Tek-Tips and kudos for a better solution than mine, as I do faintly recall in boolean logic that...
[tt]
Not A And Not B == Not (A Or B)
[/tt]
Have a Star

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Oh, if we are offering alternatives ...

[tt]=IF(COUNT(F5,C5)=2,F5-C5,"")[/tt]

 

[djieon]

@RobertPhillips

Thanks for looking at this for me....yes the end date could be in the next day...that was going to be my next thread!

Any ideas?

 

[Andrzejek]

the end date could be in the next day"

That would be hard to untangle unless you have Date and Time in a cell.
With just the time, you may establish a rule that – if you get a negative value (from 11:00pm to 4:00am) that would suggest next day. But if someone started at 8:00am and was done at 8:30am next day, how will you know that by keeping just the time?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

...or even worse, if someone started at 11:45pm and was done at 12:15am two days later.

Notice that assuming that the end time is ONE day later would result in only 30 minutes duration, if the guy actually worked more than 24 hours.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

That probably falls into a category: “That will never happen”, until it does happen, and it will be a programmer’s fault for not accommodating this scenario.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[djieon]

thanks for the replies...im happy to make it a date time cell if that makes it work?