How to convert minutes into (YY MM DD HH:mm:ss) in excel?-CodePudding

I'm using manipulating time in excel. I have the date in minutes I want to convert it into the format. YY MM DD HH mm where:

mm:Minutes
HH:Hours
DD:Days
MM:month
YY: year

Im using the following logic: For 124 minutes I have

124/60= 2 hours
124` = 4 minutes

and so on for the days and the months and the years

The units Im using are:

mm-> HH /24
HH-> MM /30
MM-> YY /12

But something is really wrong:

Does anyone have an idea what was happening? Thanks a lot, guys

CodePudding user response：

Your calculations are "really wrong" because in real life, months have anywhere between 28 and 31 days, so you cannot represent a large number of minutes as a "date" that uses variable measures for years and months.

On the assumption that as units of measure you want to use ...

Year = 360 days
month - 30 days

... you can calculate as follows:

That's also what your calculation shows. If something is "really wrong" with that, it's probably your expectation, which is based on the traditional 365.25 day year.

Maybe you want to edit your question and explain what you expect as the "really correct" result.

CodePudding user response：

In Excel dates are fractional number of days from 31-Dec-1899 (date 0 is formatted as 0/1/1900). So why don't you just add your minutes (after dividing by (24*60) to convert to days) to the earliest valid date in EXCEL (1900-01-01) and use the TEXT function to format: =DATEVALUE("1900-01-01") (21/24/60)-1 The bold part is your minutes. This is now a proper EXCEL date; you can use TEXT function to format it into any way you like, or you can use DAY(), HOUR(), etc. functions to extract parts.

Having said that I don't know what your minutes represent; What date does Full Time in Minutes:21 correspond to?