# Data Validation - Custom

time hours minutes date serial numbers

3 replies to this topic

Newbie

• Members
• 2 posts

Posted 12 November 2014 - 11:48 AM

Hi

Is it possible to create a custom formula for data validation in order to convert hours and minutes type in a cell into minutes.

Thank You

### #2 Jonathan

Jonathan

• 862 posts

Posted 12 November 2014 - 10:01 PM

Hi Clifford,

I'm not quite sure what you're trying to do here - data validation is used to confirm that the user has entered the correct value, so it can't be used to convert values.

For a video lesson that fully explains data validation, see: Expert Skills Lesson 2-7 Validate numerical data.

If you have hours and minutes values that you want to convert into a total number of minutes, you can do this by using quite simple mathematical formulas.  A formula like this should be all you need:

=(A2*60)+B2

Basic mathematical formulas like this are explained in the video lesson: Essential Skills Lesson 2-12 Create your own formulas.

It's easier to understand if you use tables and structured references to create a formula like this:

=([Hours]*60)+[Minutes]

For a walkthrough of creating a table, see the video lesson: Expert Skills Lesson 1-11 Convert a range into a table with a total row.

For an explanation of structured references, see the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

I have attached an example spreadsheet with this formula applied:

I hope this is what you were looking for, but please feel free to reply if I can offer any more assistance.

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.

Newbie

• Members
• 2 posts

Posted 13 November 2014 - 10:07 AM

Hi Jonathan

What I was hoping for was that when a user types in a cell for example 10.5 hours, by using data validation a (custom formula for that cell) would convert it into 630 minutes.

Cheers

Clifford

### #4 Jonathan

Jonathan

• 862 posts

Posted 13 November 2014 - 07:10 PM

Hi Clifford,

It isn't possible to have Excel perform calculations on the same cell when data is entered, but it is certainly possible to carry out this conversion using a formula in a separate cell.

If the user entered 10.5 hours, the formula would be:

10.5 * 60 = 630

...however, you might prefer to have your users enter the times using Excel's time notation, so instead of 10.5 hours, the user would enter 10:30 to indicate 10 hours and 30 minutes.

Entering times like this means that Excel will treat the cell as containing a date serial number. For 10:30, the serial number will be 0.4375.  For this reason, you will need to use the formula:

=(24*60)*0.4375 = 630

For a much more detailed explanation of how Excel handles time values and date serial numbers, see the following video lessons:

Expert Skills Lesson 3-7 Understand date serial numbers

Expert Skills Lesson 3-12 Enter time values and perform basic time calculations

You cannot use data validation to convert the contents of a cell, but you could use it to make sure that your users enter valid time values.

For more on how to do this, see the video lesson: Expert Skills Lesson 2-7 Validate numerical data.

I have attached an example spreadsheet that uses the formula shown above and also uses data validation to make sure that a valid time is entered in column A.

I hope this is what you're looking for.  If you need any assistance implementing these features in your workbook, please feel free to reply.

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.

### Also tagged with one or more of these keywords: time, hours, minutes, date serial numbers

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users