convert minutes to mth:dd:hh:mm problem

[song0311]

Hi there Excel SMEs,

I am working on a project that requires me to convert time in minutes to mth:dd:hh:mm format. I've tried using the format function - custom with mm:d:hh:mm, however, the numbers don't add up. For example, for 145325 minutes, I should have 3mth:10:22:05, however excel returned it as 04:09:22:05. What did I do wrong or how can I rectify this?

 

[GraH]

145325 is recognized in excel as a data value November 18th, 2297.
You have divided this value by 1440. That gives you 100.9201389. 100 as date in Excel is 9th of April, 1900. That's the 4th month. So your format is correct.
The remainder (0.9201389) is a time value that excel recognizes as 22:05:00. Again the format works as to be expected and is correct.
I guess you want to see the value as something like 3.3640 months. I have no clue, yet.

 

[DJunqueira]

You could try this format "mm/dd hh:mm:ss" that is 04/09 22:05:00.
From =145325/24/60

 

[song0311]

You could try this format "mm/dd hh:mm:ss" that is 04/09 22:05:00.
From =145325/24/60

Hi DJunqueira, thanks for your suggestion. I tried and got what you indicated. However, that does not address the issue of 145325 min does not equate to 4 months +, it's actually 3 months 10days +. As what GraH has mentioned above, the system doesn't seem to recognise that. And that is the challenge I'm trying to overcome currently

 

[FormR]

Hi, welcome to the forum!

For example, for 145325 minutes, I should have 3mth:10:22:05

You could try something like this:

Book2
	A	B
1	145325	3mth:10:22:05:00
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LET(D,A1/60/24,INT(D/30)&"mth:"&INT(MOD(D,30))&":"&TEXT(MOD(MOD(D,30),10),"HH:MM:SS"))

 

[song0311]

Hi, welcome to the forum!

You could try something like this:

Book2
	A	B
1	145325	3mth:10:22:05:00
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LET(D,A1/60/24,INT(D/30)&"mth:"&INT(MOD(D,30))&":"&TEXT(MOD(MOD(D,30),10),"HH:MM:SS"))

OMG, this is exactly what i needed! i don't actually need the seconds bit, so can i just remove ":SS" portion without affecting the formula?

 

[FormR]

so can i just remove ":SS" portion without affecting the formula?

Yes, absolutely, although I just noticed that I wrongly hard coded one of the values in the TEXT() part of the formula, you should use this version.

=LET(D,A1/60/24,INT(D/30)&"mth:"&INT(MOD(D,30))&":"&TEXT(MOD(MOD(D,30),INT(MOD(D,30))),"HH:MM"))

 

[song0311]

Thank you FormR and everyone else who helped!

 

[DJunqueira]

Refining FormR's formula a little bit.

=LET(d;MOD(A1/60/24;30);INT(d/3)&"mth:"&INT(d)&":"&TEXT(MOD(d;1);"HH:MM"))

 

[AFZAL SOHAIL]

Mr.Djunqueira for only my knowledge, i use your formula but is giving the attached error:-