Calculating Years, Months, Days, Hours, Minutes for Work Days/Hour from Minutes

Good day all,

I am hoping to get some help with a formulae. I am a complete novice, and have managed to find formulae online which allow me to calculate YMWDHMs from a large number of minutes, however, I cannot seem to find anything which would help me tweak the formula to report back in the same format when considering a working day is 8 hours, and a working week is 5 days. Can anyone help with this specific issue? And to add to this, I would also like the result to ignore the specific units when there is a zero value.

This started out as something I figured was going to be relatively simple and has mutated into the foul spawn of something wretched and putrid.

Any assistance would be gratefully received.

Thanks

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Thank you, Title updated.

Try the following in cells H3 and down:

Formula:

Please Login or Register  to view this content.

Although untested, I imagine that substituting M3 for L3 will yield the desired results for cells I3 and down.
Let us know if you have any questions.

Firstly, Happy New Year all,

Secondly, many thanks for this. And while it does give YMDHM values, it does this for conventional weeks, whereas I was looking for an 8 hour work day and 40 hour work week. I imagine this would be a considerably different number if calculated to these parameters. I simply do not have the wherewithal to figure this out so any additional help would be wholeheartedly appreciated.

Thanks again

I had assumed that the YMDHM values shown in columns H:I were basically correct and that you only wanted the zero value units removed.
I now assume that you want H4 to read 1 Days 1 Minute(s), and H6 to read 1 Days 1 Hours 37 Minute(s) is that correct?

Based on my assumption, here is a proposal that employs a lot of helper columns (N:W).
The columns marked *(adj) for minutes, hours, days, weeks, months and years are the final calculations for those units.
For example the final calculation for Day is: =IF(Q3>=28,Q3-28,IF(Q3>=21,Q3-21,IF(Q3>=14,Q3-14,IF(Q3>=7,Q3-7,Q3))))
Such that if there are 23 days in the (raw) calculation, then the final number of days will be 2 and 3 will be added to the number of weeks.
Column H is populated using: =IF(W3=0,"",W3&" Year(s)")&IF(V3=0,""," "&V3&" Month(s)")&IF(T3=0,""," "&T3&" Week(s)")&IF(R3=0,""," "&R3&" Day(s)")&IF(P3=0,""," "&P3&" Hour(s)")&IF(N3=0,""," "&N3&" Minute(s)")
Note that I have only tested with the values present in the file that was attached to post #3, so I encourage rigorous testing to find any errors.
Note that I have only worked this out for the Total Sample Time (Conventional). To get the Total Sample Time (New) a new set of helper columns would be needed where references to column L are changed to column M
Let us know if you have any questions.

I was just coming back to respond with the affirmative to your previous questions, and see that you've knocked it out the park already. Many thanks, there are no words to express my appreciation.

Regards

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

As to row 6 if when 2 is changed to 20 in F6 then the value in L6 increases by a factor of 10 so I believe the value in H6 is correct.
As to row 7 I changed the formula for Week(raw) to read: =(Q3-R3)/5, the Day(raw) formula already reads: =SUM(FLOOR(30.5*MOD(L3*12,1),1),(O3-P3)/8) so I believe that the calculation is now made based on 8 hours/day and 5 days/week.
Admittedly calculations are based on the original formula in column H of the file attached to post #3, so if that formula was not correct then mine will not be either.
We may be in a better position to help if we were provided some manually calculated results with which to compare the yields of our proposed formulas.
Let us know if you have any questions.

I was looking for an 8 hour work day and 40 hour work week.

How many hours for 1 month. is it 30 days (30*8 hrs) or 4 weeks (4*5*8 hrs).
Similarly For 1 year. Is It 365 days or 12 months.
Pl clarify.

Thank you for reaching out, and to JeteMc.

For the hours in one month, I would say the 4*5*8 hrs.
The year would be 12 months, or more accurately 52 weeks * (5 days * 8 hrs) if that makes sense.

Hope this clarifies.

Regards

Shads