• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Seahorse

Seahorse

Member
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.
 

Attachments

Last edited:
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"))
 
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?
 
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
 
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.:awesome:

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
 

Attachments

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,
 
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.
 
You must log in or register to reply here.
Back
Top