Excel - Showing Weeks In dd:hh:mm Format

  Jester2K II 16:52 05 Nov 2003

Further to a few past threads i now need to amend a time sheet to show totals of times.

However there is a LOT of data to sum.

Is there a way to show weeks or months in dd:hh:mm format

ie. if the total was 245 hours it shows as 10:05:00 (10 days, 5 hours, 0 minutes) at the moment.

Can this be shown as 01:03:05:00 ?? (1 week, 3 days, 5 hours, 0 minutes)

  recap 18:48 05 Nov 2003

One for VoG I think?

  VoG II 18:57 05 Nov 2003

I think that to avoid a horrendously complex formula you need some intermediate cells (that can be hidden).

With 245 in B7

C7 =ROUNDDOWN(B7/168,0) (weeks)

D7 =ROUNDDOWN((B7-C7*168)/24,0) (days)

E7 =B7-C7*168-D7*24

Then to display the result in your desired format:


To carry on from VoG, the horrendous formula (HF) is as follows:

=TEXT(ROUNDDOWN(B10/168,0),0)&"w "&TEXT(ROUNDDOWN(B10/24,0)-7*ROUNDDOWN(B10/168,0),0)&"d "&TEXT(INT(MOD(B10,24)),0)&"h "&TEXT((B10-INT(B10))*60,0)&"m"

Where the Cells in the range B1 to B9 are formatted in the usual [h]:mm.

To simplify matters then B10 is formatted as a number with two decimal places and contains the following formula =sum(B1:b9)*24 - this just converts the sum into decimal hours and minutes.

The HF is placed in B11 and Row10 is hidden.

If you wish to retain the 01:03:05:00 format then the formula would be changed to:



P.S. As months vary in length that element is impossible without incorporating dates, and you are on your own! LOL

  VoG II 22:00 05 Nov 2003

I'm starting to plan my retirement.

My hat off to you Whisperer!

P.S. I had come up with the better formula (using MOD) but posted the wrong one (honest)!

  Jester2K II 22:12 05 Nov 2003

VoG & Whisperer - thanks but i'll have to look at this Stella-less..... (one for the morning...)

Cheers - i'll let you know how i get on...

  VoG II 22:57 05 Nov 2003

Please, only how you get on with the Excel file. How you get on with Madame Stella is a private matter. LOL!

  Jester2K II 23:09 05 Nov 2003

Ok On an alaternative track - how can i get it to count months? I notice the currrent dd:hh:mm goes to a maximum of 31.23.59 then resets.

If i added 1 minute to the above time can i get it to show


as in

1 Month (32 days)
0 Days
0 Hours
0 Minutes

If this involves a long formaula please don't worry (if you like the challenge go ahead) but if the above can be achieved then we can assume that each "month" is 32 days

ie 03:05:15:25

would be

3 "months" or 3 x 32 = 96 days
5 days (total 96 + 5 = 101 days)
15 hours
25 minutes


  VoG II 23:17 05 Nov 2003

Either you or I (or probably both) have had enough of Madame Stella for tonight.

A month is approximately 30.6 days (not 32).

I will respond tomorrow evening (work hours are out of the question at the moment - guy left, VoG gets old job plus guy's job = one stressed VoG).


  Jester2K II 23:18 05 Nov 2003

Excellent - the Stella is working fine. I printed instead of Spell Checking!!!! I will have a go at the above tomorrow...

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HTC U12 Plus review: Hands-on

Brilliant (and Weird) World Cup 2018 Art and Design Projects

Best Android emulators for Mac

TV & Streaming : comment regarder Roland Garros ?