Duration (Time), Custom Date and different than expected outputs

[Seahorse]

So I have a plan that has start and end date/times using one of the custom options - dd mm yy\ hh:mm.

What want to do is add new column that checks there is a start date time group, end date time group and the supplies the answer as 1 Day 05:20 or is blank.

=IF(OR(D2-C2<=0,C2<=0),"",INT(D2-C2)&" days "&TEXT(D2-C2,"h"" hrs ""m"" mins """))

Output date & time sorted:

=IF(OR(D18-C18<=0,C18<=0),"",INT(D18-C18)&" days "&TEXT(D18-C18,"hh"":""mm"""""))

I have not yet sorted the output time formatting as I have run into a problem where editing an exisiting date then adds seconds, and these don't seem to calculate at all. For example changing 12 05 14 10:00 to 16 05 14 10:00 displays 16 05 14 10:00:00.

Example is attached, I would appreciate a some help on this one.

 

[Hui]

Two small problems

1. D8 is Text and not a date, i/d always suggest entering dates as dd/mm/yyyy hh:mm:ss
eg; 1/07/2014 12:00:00 am

2. In E8 use the formula: =IF(OR(D4-C4<=0,C4<=0),"",INT(D4-C4)&" days "&TEXT((D8-C8)-INT(D8-C8),"[h]:mm:ss"))

 

[Seahorse]

This works as a custom date - dd/mm/yyyy hh:mm:ss however the output with the new formulas is still #value? apart from line 4?

 

[Seahorse]

Despite further fiddling, the date time custom number issue remains as do partially working formulas? Further more the suggested replacement misses several hours in it's answer?

 

[Hui]

D4 should be:
=IF(OR(D4-C4<=0,C4<=0),"",INT(D4-C4)&" days "&TEXT((D4-C4)-INT(D4-C4),"[h]:mm:ss"))

and your dates should be entered as: 12/05/2014 10:00:00 PM
not with spaces

You can apply a custom number format as dd mm yyyy but they won't be recognised if you enter the dates with spaces
But if you enter them with /'s they will be displayed with spaces

 

[Seahorse]

Hui,

sorry to be dense it's working correctly now, it's a shame about the clunky entry date/time format but it does automate this rather painful task I have stripped the seconds out, as they are of no consequence. Fixed example attached for ease of reference for others.

Final problem is with the TBC causing a #VALUE! error. I understand it because we are mixing text and numbers, so I have tried to circumvent this using TBC in G1, but same error? both of the below fail:

=IF(OR(D6-C6<=0,C6<=0,C6="TBC"),"",INT(D6-C6)&" days "&TEXT((D6-C6)-INT(D6-C6),"[hh]:mm"))
=IF(OR(D6-C6<=0,C6<=0,C6=G1),"",INT(D6-C6)&" days "&TEXT((D6-C6)-INT(D6-C6),"[hh]:mm"))

Thanks
Mike

 

[Somendra Misra]

Hi Mike,

Your formula is giving error since you have text TBA in C6 & D6. What output you expect if there is this text in either of the cells?

Regards,

 

[Seahorse]

If $C contains TBA I simply want it to return "", the same as when there is no date/time present in the cell. Column E is either the duration of the task from Cols C & D or blank.

 

[NARAYANK991]

Hi Mike ,

Something like this ?

Narayan

 

[Seahorse]

Perfect, we're there dude. Thank you both