• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need to convert the sum of total Minutes, Hours and days into Years, days, hours and minutes.

I have made a small file that contains information about the sum of quantities of time(Minutes, Hrs, and Days). As you can see, column A2:A15 has the data that work with. The column B2:B15 has the quantities related to A2:A15. I need that column "C" (From C2:C15) to tell me the result of the total of minutes, hours, and days. And I need that "C16 " to display the total of Column from "C2:C15" but to tell how many years, months, days, hours, and minutes. IS THIS POSSIBLE?

I am sending an image and the small file I am using.

Thank you so much in advance.
 

Attachments

bosco_yip

Excel Ninja
The formula can be calculated accurate on Days, Hours and Minutes only, but not in month and year

So, please reply the calculation of number of days in :

1 year =??? Days and 1 month =?? Days

Regards
 
Last edited:

bosco_yip

Excel Ninja
Here is a formula solution in convert the sum into Years, Months, days, hours and minutes.

Given that a year is based on 365 days and a month is 30.33 days

1] Select C2, and create the name ranges as per attachment.

2] In C2, formula copied down :

=INT(DayNo/365)&" Years, "&INT(MOD(DayNo,365)/30.33)&" Months, "&TEXT(DayNo-(INT(DayNo/30.33)*30.33),"d ""Days"" h ""Hours"" m ""Minutes""")

3] In C16, enter formula :

=INT(TtlDayNo/365)&" Years, "&INT(MOD(TtlDayNo,365)/30.33)&" Months, "&TEXT(TtlDayNo-(INT(TtlDayNo/30.33)*30.33),"d ""Days"" h ""Hours"" m ""Minutes""")

77511
 

Attachments

Last edited:
Top