problems displaying time formats

[merlin777]

I'm trying to enter a duration in years, days, hours, minutes and seconds then apply a percentage and display the results as NN years NN days NN hours NN minutes NN seconds.

I've applied the custom time format yy "years" d "days" h "hours" m "minutes" s "seconds" For now I'm not applying the percentage, just trying to get the results to display properly.

There are several problems I'm getting.

When I type 01:02:03 into my input cell it displays as 01:02:03 justified right, like a numeric value. The results cell displays as 00 years 00 days 1 hours 2 minutes 3 seconds.

When I type 01:02:03:04:05 into my input cell it displays as 01:02:03:04:05 justified left, like text and the results cell displays 01:02:03:04:05, also justified left.

Whatever I've tried I can't get it to display with full text if I enter more than nn:nn:nn.
When i use 'y' in the custom format it still returns 2 digits i.e. 00 or 01 etc and not a single digit.
Sometimes for no reason I can work out, it was displaying the input cell as '1900/1/1 01:02:03 ' but as I write this I can't get it to do that again.

I assume this is all down to the way I am entering my time duration?

What am I doing wrong?

 

[RoryA]

01:02:03:04:05 is not a valid date/time format in any part of the world that I'm aware of.

 

[merlin777]

not date - time.In broadcasting we use this (not the years though) and we often add and extra one on the end for frames.

 

[RoryA]

My point is that it means nothing to Excel.

 

[merlin777]

how should I enter a time duration then?

 

[RoryA]

Excel won't recognize anything more than hours:minutes:seconds as an actual time value. If you need to include years and days, you'd need to enter it as a decimal where 1 = 1 day and 1 hour is 1/24. Or you can enter the days in a separate cell to the time, then calculate the appropriate duration from that using a formula.

 

[merlin777]

can't get years and days to display using custom format

I'm trying to display a time duration as n years n days n hours n minutes n seconds

I'm applying the custom time formatting y "years" d "days" h "hours" m "minutes" s "seconds"

I can't work out how to enter a duration that includes all these so I am calculating it by summing the number years * 365, number of days , number of hours / 24, minutes / 1440 and seconds / 86400 and applying the custom format to the result.

However, with my test number made from 1 year, 2 days, 3 hours, 4 minutes and 5 seconds I'm getting this:

00 years 31 days 3 hours 4 minutes 5 seconds

It looks like I'm making a mistake involving months?

and for some reason, even though I'm only using one 'y' in the custom format it gives me 2 digits.

Help!

 

[merlin777]

Thanks for that. I reposted with my results a bit more clearly. Calculated it as you suggested but still not got it right...

Excel won't recognize anything more than hours:minutes:seconds as an actual time value. If you need to include years and days, you'd need to enter it as a decimal where 1 = 1 day and 1 hour is 1/24. Or you can enter the days in a separate cell to the time, then calculate the appropriate duration from that using a formula.

 

[merlin777]

Re: can't get years and days to display using custom format

Ok hands up to a bit of finger trouble... After correcting a formula the result i'm getting is:

01 years 1 days 3 hours 4 minutes 5 seconds

So, I'm close but there should be 2 days and I don't want a leading zero on the year.

This

I'm trying to display a time duration as n years n days n hours n minutes n seconds

I'm applying the custom time formatting y "years" d "days" h "hours" m "minutes" s "seconds"

I can't work out how to enter a duration that includes all these so I am calculating it by summing the number years * 365, number of days , number of hours / 24, minutes / 1440 and seconds / 86400 and applying the custom format to the result.

However, with my test number made from 1 year, 2 days, 3 hours, 4 minutes and 5 seconds I'm getting this:

00 years 31 days 3 hours 4 minutes 5 seconds

It looks like I'm making a mistake involving months?

and for some reason, even though I'm only using one 'y' in the custom format it gives me 2 digits.

Help!

 

[Richard U]

The best way to do this would be to use the worksheet change event and have it fire off when the range of cells you were entering the data was updated via application intersect.

Then, have a macro fire off to concatenate the results into a string format in the cell