Jump to content


Photo

Data Validation - Custom

time hours minutes date serial numbers

  • Please log in to reply
3 replies to this topic

#1 Clifford Appadoo

Clifford Appadoo

    Newbie

  • Members
  • Pip
  • 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

 

Clifford Appadoo



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 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:

 

Attached File  hoursminutes.xlsx   9.26KB   131 downloads

hoursminutes.png

 

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.


#3 Clifford Appadoo

Clifford Appadoo

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 13 November 2014 - 10:07 AM

Hi Jonathan

 

Thanks for the reply.

 

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

    Forums Administrator

  • Administrators
  • 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.

 

Attached File  TimeValidation.xlsx   9.75KB   120 downloads

timevalidation.png

 

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users