Number formatting query for a formula returning a duration of more than 24 hours.

[NewOrderFac33]

Good afternoon,

I came across Barry Houdini's formula to use NetWorkDays with some additional parameters ro allow you to work out the amount of elapsed "working" time between two date/times thus:

=(NETWORKDAYS(B6,C6)-1)*("6 PM"-"9 AM")+MOD(C6,1)-MOD(B6,1)

where B6 is the Start Time, C6 is the End Time, "6 PM" is the end of the working day and "9 AM" is the start of the working day (no, I don't know how he worked it out, either!)

For my example, I use:

[h]" hrs":mm" mins "

to format the results.

With a start date of Monday 09/09/13 16:00 and an end date of Tuesday 10/09/2013 10:15 I get a correct result of 3 hrs 15 minutes (Mon 16:00-18:00 + Tue 09:00-10:15)

However, when the durations exceed 24 hours, although the numbers of hours and minutes returned are correct, what I really want to see is (for example)

Start Time Monday 09/09/2013, End Time Wednesday 11/09/2013 18:00 showing as "01 days 03 hrs 00 mins" instead of "27 hrs 00 mins"

Is this even possible?

As usual, thanks in advance.

Pete

 

[AlphaFrog]

Use a custom cell format something like this.

d" days" h" hrs":mm" mins "

It's only good up to 31 days though.

 

[NewOrderFac33]

Thanks for your assistance, which made me realise that my question was dumb in the first place as if I'm trying to calculate working hours only, any display of "days" is actually meaningless!
It's amazing what a good night's sleep will do..!