• 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

  • Times Capture.JPG
    Times Capture.JPG
    93.1 KB · Views: 13
  • Times Convertion.xlsm
    10.3 KB · Views: 8
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:
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

  • Times Convertion (BY).xlsm
    12.1 KB · Views: 8
Last edited:
Back
Top