Excel - Showing Weeks In dd:hh:mm Format

Jester2K II 16:52 05 Nov 03
Locked

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 03

One for VoG I think?

VoG II 18:57 05 Nov 03

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:

=TEXT(C7,"00")&":"&TEXT(D7,"00")&":"&TEXT(E7,"00")&":00"

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:

=TEXT(ROUNDDOWN(B10/168,0),"00")&":"&TEXT(ROUNDDOWN(B10/24,0)-7*ROUNDDOWN(B10/168,0),"00")&":"&TEXT(INT(MOD(B10,24)),"00")&":"&TEXT((B10-INT(B10))*60,"00")

HTH

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 03

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 03

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 03

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 03

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

01:00:00:00

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 03

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

Cheers!

Jester2K II 23:18 05 Nov 03

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.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…