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

[LWLD]

Good day all,

I am hoping to get some help with a formulae. I recently got help with another problem, and I am hoping y'all can help with this one also. I 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

As you can see from the table H7 is returning a value of just over 6 years, however, with a working day and week, this should be much higher. I have a couple of helper rows L and M, though, it's been so long since I worked on this sheet, I can no longer remember the exact reason for each.

Work Hours Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Work Week/Hour Calculator
2	№ of Samples	№ of Sides	№ of FM per Side	№ of Imaging Combinations	№ of FM Images	Total Time per FM (Conventional)	Total Time per FM (New)	Total Sample Time (Conventional)	Total Sample Time (New)
3	1	2	25	2	100	1 minute(s)	0.6 seconds	1 hours 40 minute(s)	0 hours 2 minute(s)			0.000190259	3.80518E-06
4	5	2	8	3	240	2 minute(s)	0.25 seconds	8 hours 1 minute(s)	0 hours 15 minute(s)			0.000913242	2.85388E-05
5	3	1	4	2	24	5 minute(s)	2.5 seconds	2 hours 0 minute(s)	0 hours 3 minute(s)			0.000228311	5.70776E-06
6	12	2	6	2	288	2 minute(s)	0.21 seconds	9 hours 37 minute(s)	0 hours 24 minute(s)			0.00109589	4.56621E-05
7	100000	2	4	2	1600000	2 minute(s)	0.01 seconds	6 Years 1 Months 1 Days 7 hours 27 minute(s)	4 Months 17 Days 6 hours 27 minute(s)			6.088280061	0.380517504
8					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
9					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
10					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
11					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
12					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
13					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
14					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
15					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
16					0		0 seconds	0 hours 0 minute(s)	0 hours 0 minute(s)			0	0
AARI Calculator

Cell Formulas
Range	Formula
G3:G16	G3	=ROUNDUP(IFERROR(60/E3,0),2)&" seconds"
H3:I16	H3	=IF(INT(L3)*FLOOR(12*MOD(L3,1),1)=0,"",INT(L3)&" Years ")&IF(FLOOR(12*MOD(L3,1),1)=0,"",FLOOR(12*MOD(L3,1),1)&" Months ")&IF(FLOOR(30.5*MOD(L3*12,1),1)=0,"",FLOOR(30.5*MOD(L3*12,1),1)&" Days ")&TEXT(MOD(L3*12*30.5,1),"h"" hours ""m"" minute(s) """)
L3:L16	L3	=(A3*B3*C3*D3*F3)/525600
M3:M16	M3	=A3*B3*D3*0.5/525600
E3:E16	E3	=A3*B3*C3*D3

 

[Jeffrey Mahoney]

I just find it easier to create helper columns, it's much easier to read and manage. I created a tale for the conventional calculated time. See what you think

Work Week Calc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Work Week/Hour Calculator										Seconds per Unit:	31536000	2628000	604800	86400	3600	60
2												Yr	Mnth	Wk	Day	Hr	Min
3	№ of Samples	№ of Sides	№ of FM per Side	№ of Imaging Combinations	№ of FM Images	Total Time per FM (Conventional)	Total Time per FM (New)	Total Sample Time (Conventional)	Total Sample Time (New)		Total Conventional Seconds	Yrs	Mnths	Wks	Days	Hrs	Mins
4	1	2	25	2	100	1 minute(s)	0.6 seconds	1 Min	0 hours 0 minute(s)		100	0	0	0	0	0	1
5	5	2	8	3	240	2 minute(s)	0.25 seconds	8 Mins	0 hours 0 minute(s)		480	0	0	0	0	0	8
6	3	1	4	2	24	5 minute(s)	2.5 seconds	2 Mins	0 hours 0 minute(s)		120	0	0	0	0	0	2
7	12	2	6	2	288	2 minute(s)	0.21 seconds	9 Mins	0 hours 0 minute(s)		576	0	0	0	0	0	9
8	100,000	2	4	2	1600000	2 minute(s)	0.01 seconds	1 Mnth, , 6 Days, 14 Hrs, 53 Mins	0 hours 0 minute(s)		3,200,000	0	1	0	6	14	53
9	5,000,000	2	4	2	80000000	3 minute(s)	0.01 seconds	7 Yrs, 7 Mnths, 1 Wk, 2 Days, 20 Hrs, 40 Mins	0 hours 0 minute(s)		240,000,000	7	7	1	2	20	40
10					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
11					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
12					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
13					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
14					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
15					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
16					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
17					0		0 seconds		0 hours 0 minute(s)		0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
L1	L1	=60*60*24*365
M1	M1	=L1/12
N1	N1	=60*60*24*7
O1	O1	=60*60*24
P1	P1	=60*60
G4:G17	G4	=ROUNDUP(IFERROR(60/E4,0),2)&" seconds"
H4:H17	H4	=IFS(L4>1,L4&L$3,L4=1,L4&L$2,L4=0,"")&IF(SUM($L4:L4)>0,", ","")&IFS(M4>1,M4&M$3,M4=1,M4&M$2,M4=0,"")&IF(SUM($L4:M4)>0,", ","")&IFS(N4>1,N4&N$3,N4=1,N4&N$2,N4=0,"")&IF(SUM($L4:N4)>0,", ","")&IFS(O4>1,O4&O$3,O4=1,O4&O$2,O4=0,"")&IF(SUM($L4:O4)>0,", ","")&IFS(P4>1,P4&P$3,P4=1,P4&P$2,P4=0,"")&IF(SUM($L4:P4)>0,", ","")&IFS(Q4>1,Q4&Q$3,Q4=1,Q4&Q$2,Q4=0,"")
I4:I17	I4	=IF(INT(M4)*FLOOR(12*MOD(M4,1),1)=0,"",INT(M4)&" Years ")&IF(FLOOR(12*MOD(M4,1),1)=0,"",FLOOR(12*MOD(M4,1),1)&" Months ")&IF(FLOOR(30.5*MOD(M4*12,1),1)=0,"",FLOOR(30.5*MOD(M4*12,1),1)&" Days ")&TEXT(MOD(M4*12*30.5,1),"h"" hours ""m"" minute(s) """)
K4:K17	K4	=E4*F4
L4:L17	L4	=INT($K4/L$1)
M4:Q17	M4	=INT(($K4-SUMPRODUCT($L$1:L$1,$L4:L4))/M$1)
E4:E17	E4	=A4*B4*C4*D4

 

[LWLD]

Good day @Jeffrey Mahoney, thanks for the input. The problem seems to be that these are not accounting for a work week (40 hours) and a work week (5 days).

In my table, H& comes out at just over 6 years, however, this is if we were calculating at 24 hours a day 7 days a week. Where in reality, it should be around 3 times as long, if not more.

I'll try out your formulae and see what's what.

Thanks again.

 

[Jeffrey Mahoney]

Change the number of seconds for each period in the top row of the helper table

 

[LWLD]

Hi, @Jeffrey Mahoney, I managed to get those changed and working. Thanks. I tried to use the same formulae (row H) for row I, but can't seem to get anything other than zeroes across the board. Is a roundup required somewhere?

 

[Jeffrey Mahoney]

I don't know about getting zeros because the table I posted shows proper values. Make sure all the supporting values in columns A-F are real numbers, not text. For instance, column F has values that are number formatted to look like 1 Minute or 12 minutes, but in fact they are whole integers. Make sure you put all the formulas I provided in the right columns. If they were inserted into the wrong column and then you deleted a column afterward so that it showed the same column as I, then things are not going to work.

 

[LWLD]

Got it sorted, thanks for all the help.

 

[LWLD]

On the back of this, I'm trying to calculate the difference between the two time-frames and although some work, some are returning negative values, how would I address this. I've included the helper tables I am using.

Work Hours Calculator - Copy.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Seconds per Unit	6912000	576000	144000	28800	3600	60		Seconds per Unit	6912000	576000	144000	28800	3600	60
2		Year(s)	Month(s)	Week(s)	Day(s)	Hour(s)	Min(s)			Year(s)	Month(s)	Week(s)	Day(s)	Hour(s)	Min(s)
3	Total Conventional Seconds	Year(s)	Month(s)	Week(s)	Day(s)	Hour(s)	Min(s)		Total New Seconds	Year(s)	Month(s)	Week(s)	Day(s)	Hour(s)	Min(s)			Year(s)	Month(s)	Week(s)	Day(s)	Hour(s)	Min(s)
4	12000	0	0	0	0	3	20		240.00	0	0	0	0	0	4			0	0	0	0	3	16
5	28800	0	0	0	1	0	0		1800.00	0	0	0	0	0	30			-1	-1	-1	0	-1	30
6	7200	0	0	0	0	2	0		360.00	0	0	0	0	0	6			-1	-1	-1	-1	1	54
7	34560	0	0	0	1	1	36		2880.00	0	0	0	0	0	48			-1	-1	-1	0	0	48
8	192000000	27	9	1	1	5	20		24000000.00	3	5	2	3	2	40			21	1	1	2	2	40
AARI Calculator

Cell Formulas
Range	Formula
L1,T1	L1	=60*60*8*5*4*12
M1,U1	M1	=60*60*8*5*4
N1,V1	N1	=60*60*8*5
O1,W1	O1	=60*60*8
P1,X1	P1	=60*60
K4:K8	K4	=E4*F4*60
L4:L8	L4	=INT($K4/L$1)
M4:Q8	M4	=INT(($K4-SUMPRODUCT($L$1:L$1,$L4:L4))/M$1)
S4:S8	S4	=ROUNDUP(IFERROR(A4*B4*D4*60,0),2)
T4:T8	T4	=INT($S4/T$1)
U4:Y8	U4	=INT(($S4-SUMPRODUCT($T$1:T$1,$T4:T4))/U$1)
AB4:AB8	AB4	=L4-T4+INT((M4-U4)/12)+INT((N4-V4)/4)+INT((O4-W4)/5)+INT((P4-X4)/8)+INT((Q4-Y4)/60)
AC4:AC8	AC4	=MOD(M4-U4,12)+INT((N4-V4)/4)+INT((O4-W4)/5)+INT((P4-X4)/8)+INT((Q4-Y4)/60)
AD4:AD8	AD4	=MOD(N4-V4,4)+INT((O4-W4)/5)+INT((P4-X4)/8)+INT((Q4-Y4)/60)
AE4:AE8	AE4	=MOD(O4-W4,5)+INT((P4-X4)/8)+INT((Q4-Y4)/60)
AF4:AF8	AF4	=MOD(P4-X4,8)+INT((Q4-Y4)/60)
AG4:AG8	AG4	=MOD(Q4-Y4,60)