Jump to content


Photo

Converting minutes to hours and minutes -- having trouble

INT paste values MOD MROUND SUMIF date serial numbers

  • Please log in to reply
3 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 05 November 2015 - 11:10 PM

I'm having trouble figuring out how to make the total sum of time selected display in the appropriate format/value.
 
The formulas (using SUMIFS) in D14 and D48 operate the same way. Whenever someone selects "Yes" in column E, that makes the value to the left of it show up in the calculations for these cells. The goal is to calculate the total duration for each video or presentation viewed.
 
In D14, it currently says "0:00" and I don't understand why. It showed this way when I used the INT function and divided by 60. I was hoping that would give me hours.
 
I was then going to use the MOD function to work out the minutes and then have the total show to the nearest quarter hour.
 
Attached File  PDU calculation for PM Day_MASTER.xlsx   15.4KB   104 downloads


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 06 November 2015 - 12:46 PM

Hi phoenixrizing,

 

I've taken a look, and it looks like you're currently using the hours as minutes and the minutes as seconds. While you could make this work with the MOD function and some multiplication, it would make things much easier if you revise the timestamps so that they actually reflect the amount of time they indicate.

 

For example, to indicate 25 minutes and 37 seconds, you're currently using the date serial number 1.07, which indicates 01:37 on the 1st of January 1900 (25 hours and 37 minutes). I've changed this to 0.02, which indicates 00:25:37 on the 0'th of January 1900 - a true value of 25 minutes and 37 seconds.

 

Time serial numbers like this are fully explained and demonstrated in the video tutorial: Expert Skills Lesson 3-12 Enter time values and perform basic time calculations.

 

Now that the times indicate the actual number of minutes and seconds, you should be able to total them with the following SUMIF function alone, with no need for anything more complicated:

=SUMIF(Table1[Viewed],"Yes",Table1[[Mins ]])

 

For a walkthrough of the SUMIF function, see the video tutorial: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

 

I have attached a copy of your workbook with the changes applied:

 

Attached File  PDU.xlsx   15.91KB   146 downloads

ProjectManagement.png

 

I hope this is what you needed, but please feel free to reply if there's anything else.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 06 November 2015 - 09:36 PM

The time was copied from a Word document and pasted into Excel by a coworker, so my guess is that the dates must have been Excel interpreting the values that way.
 
I applied your solution to my original file and it's working fine (to a point). But to get there I had to retype the time in all of the cells as "0:00:00", replacing with the actual values of course. Is that what you meant when you said that you had changed it?
 
I also changed the formatting to m:ss which I know is different than yours, but since none of the individual times exceeded one hour it wasn't necessary to show.
 
Questions
  1. With this amount of data it wasn't too bad having to retype the time, but is retyping the most efficient way or is there some formula or formatting that I overlooked to make it what it needed to be? Though I tried different formats, they did not yield the desired result.
     
  2. Attached is my revised file. I've added more calculations at the bottom of the worksheet to show subsets of the data: hours, minutes (to the nearest 15-minute increment), and how many hours/minutes for each subcategory (Leadership, etc.). I used SUMIFS to determine the calculations for the subcategories between the two tables and totaled that up with a SUM, but as you can see they're all showing up as zeroes (cells D53:D55). Changing the number format didn't help either.

Again, my goal is to put in cell D58 the total number of hours and the remaining minutes to be in .25, .50, or .75 (e.g., 15.75, 13.25), which is based on the number of keynotes/videos a person has viewed, which is determined by a "yes" in column E.

 

I appreciate your help. :)

 

Attached File  PDU calculation for PM Day_jonathan2.xlsx   17.08KB   142 downloads



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 06 November 2015 - 10:31 PM

Hi phoenixrizing,

 

The key to all of this is understanding Excel's date serial numbers. I often find it helpful to review the video lesson that explains them, as they are one of Excel's more difficult concepts. You can find a full explanation of date serial numbers in the video lesson: Expert Skills Lesson 3-7 Understand Excel date serial numbers and custom date formats.

 

If you remember that a date serial number is a decimal number indicating days and fractions of days, the answer to the first problem becomes clear:

 

In cell D6, you have the serial number 1.37, representing 1.37 days or 32 hours and 58 minutes. To mathematically change this into 32 minutes and 58 seconds, all you need to do is divide it by 60.

1.37 / 60 = 0.02, which represents 32 minutes and 58 seconds.

 

You could add a column that converts the dates and then hide the original 'raw' dates, or you could use Excel's Paste Values feature to overwrite the original 'raw' dates.

Paste Values is demonstrated in the video tutorial: Essential Skills Lesson 3-5 Use Paste Values and increase or decrease decimal places displayed.

 

The reason your SUMIFS functions in cells D53:D55 aren't working is that the names you're using as lookups in column C have colons at the end, meaning that they don't match the values in the tables. The formulas work perfectly once the lookup values have been corrected.

 

To answer your final question, we just need to think back again to how date serial numbers work. The date serial numbers we get will always be a decimal number indicating the number of days. To extract the number of hours from this, we need to multiply it by 24. You can also round the result to a whole number using the INT function:

=INT(D60*24)

 

There's more information about the INT function in the video tutorial: Expert Skills Lesson 3-9 Use the Excel DATEDIF function and YEARFRAC function.

 

Extracting the minutes is a little more complicated, but quite straightforward now that we know how to extract the number of hours. To get the total number of minutes, we need to multiply the serial number by 24 to get the number of hours and then multiply the result by 60. You can then use the MOD function to get the number of minutes remaining after the hours:

=MOD((D60*24)*60,60)

 

For an in-depth look at the MOD function, see the video lesson: Expert Skills Lesson 1-7 Apply an advanced filter with function-driven criteria.

 

...however, from your description it sounds like you didn't really want to extract the number of minutes. Instead, you want to return a decimal number indicating fractions of an hour, for example 0.75 to indicate 45 minutes. You could get this by working backwards from the number of minutes, but it's more straightforward to skip the step of extracting the minutes altogether.

 

We know that multiplying the date serial number by 24 will get a decimal number indicating the number of hours. For example, 11.69 means 11 hours and 41 minutes. The .69 is actually exactly what you want; a decimal fraction indicating the number of minutes as a fraction of a whole hour. To extract only the fractional part of the number, you can use MOD again as follows:

=MOD(D60*24,1)

 

Finally, you wanted to round the result to the nearest 0.25, and you can do that easily with the MROUND function:

=MROUND(MOD(D60*24,1),0.25)

 

MROUND is demonstrated in the video tutorial: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

I have attached another copy of your workbook with all of the new formulas applied:

 

Attached File  PDU3.xlsx   17.34KB   97 downloads

PDU2.png

 

I hope this clears everything up, but please feel free to reply again if there's anything still outstanding.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users