Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA vs Time fields
Hey all, sort of teaching myself excel and vba to simplify some work, done a
lot of reading but I'm not getting the hang of one aspect. I need to have two time fields in my form, in which the user will enter a value of minutes and seconds, in the format of mm:ss, have it stored in a cell pointed to by other factors (arbitrarily use A1 and B1 for example purposes) with the ability to be mathematically manipulated by fields filled with integer values (A2, B2, C1) with results stored in field (D1 such that D1 = ((A1 * A2) + (B1 * B2))/C1 I think the core problem I'm having is getting the Data entered into the TextBox to translate as not a string, and am a little lost, I believe the math will work fine whats it's actually a time in excel's view, so it's really the VBA code side of things I need. Thanks for any advice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA vs Time fields
Hi CDate function converts recognizable date strings to dates that can be
used in the maths. Dim strdate As String Dim dateValue As Date Examples:- strdate = "2 March 2008" 'Can be this format in string strdate = "March 2 2008" 'or this format in string 'If using following date format in string then must be as per date format 'used by uour computer for your region strdate = "2/3/08" dateValue = CDate(strdate) 'Converts to a date that can be used with maths -- Regards, OssieMac "Cavalierfool" wrote: Hey all, sort of teaching myself excel and vba to simplify some work, done a lot of reading but I'm not getting the hang of one aspect. I need to have two time fields in my form, in which the user will enter a value of minutes and seconds, in the format of mm:ss, have it stored in a cell pointed to by other factors (arbitrarily use A1 and B1 for example purposes) with the ability to be mathematically manipulated by fields filled with integer values (A2, B2, C1) with results stored in field (D1 such that D1 = ((A1 * A2) + (B1 * B2))/C1 I think the core problem I'm having is getting the Data entered into the TextBox to translate as not a string, and am a little lost, I believe the math will work fine whats it's actually a time in excel's view, so it's really the VBA code side of things I need. Thanks for any advice. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA vs Time fields
Excel date format starts at Jan 1, 1900 with one day equalling. Every 24
hours equals 1. To get the next day simply add one to the present date. To get the next week add 7. Make sure the cells are formatted as a date other wise you get the number equivalent which looks strange. Midnight is the start of the day. Each hour is 1/24 (1 day divided by 24). Each minute is 1/(24 * 60) = 1/1440. "OssieMac" wrote: Hi CDate function converts recognizable date strings to dates that can be used in the maths. Dim strdate As String Dim dateValue As Date Examples:- strdate = "2 March 2008" 'Can be this format in string strdate = "March 2 2008" 'or this format in string 'If using following date format in string then must be as per date format 'used by uour computer for your region strdate = "2/3/08" dateValue = CDate(strdate) 'Converts to a date that can be used with maths -- Regards, OssieMac "Cavalierfool" wrote: Hey all, sort of teaching myself excel and vba to simplify some work, done a lot of reading but I'm not getting the hang of one aspect. I need to have two time fields in my form, in which the user will enter a value of minutes and seconds, in the format of mm:ss, have it stored in a cell pointed to by other factors (arbitrarily use A1 and B1 for example purposes) with the ability to be mathematically manipulated by fields filled with integer values (A2, B2, C1) with results stored in field (D1 such that D1 = ((A1 * A2) + (B1 * B2))/C1 I think the core problem I'm having is getting the Data entered into the TextBox to translate as not a string, and am a little lost, I believe the math will work fine whats it's actually a time in excel's view, so it's really the VBA code side of things I need. Thanks for any advice. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA vs Time fields
Hi again,
I completely forgot earlier to include that CDate also handles converting time. I copied the example from my multitude of examples and only realized that I had not added the time bit when I read Joel's answer. CDate can convert times entered as strings if they are in the correct format with the colon and the string contains hours. If entered as minutes and seconds only then you need to concatenate the hours and the entered string before applying CDate. Examples:- strTime = "59:11" 'As entered by user in text box. strTime = "0:" & strTime 'Concatenate hrs with the mins and secs entered dateValue = CDate(strTime) 'convert to date that can be manipulated with maths Range("A1") = dateValue The above would display as 12:59:11 AM in cell A1. However, if you use number format and create a custom format as mm:ss then it will display as minutes and seconds only. Also note that if you are manipulating times on a worksheet and the minutes exceed 59 minutes, using a custom format with square brackets around the mm you can force it to continue displaying in mins and secs. Example:- [mm]:ss You can do the same with hours to prevent it thinking it is the next day if they go past 24 hours. [hh]:mm:ss. However, CDate in VBA cannot handle the seconds or minutes if they exceed 59 and cannot handle hours exceeding 24. -- Regards, OssieMac "Cavalierfool" wrote: Hey all, sort of teaching myself excel and vba to simplify some work, done a lot of reading but I'm not getting the hang of one aspect. I need to have two time fields in my form, in which the user will enter a value of minutes and seconds, in the format of mm:ss, have it stored in a cell pointed to by other factors (arbitrarily use A1 and B1 for example purposes) with the ability to be mathematically manipulated by fields filled with integer values (A2, B2, C1) with results stored in field (D1 such that D1 = ((A1 * A2) + (B1 * B2))/C1 I think the core problem I'm having is getting the Data entered into the TextBox to translate as not a string, and am a little lost, I believe the math will work fine whats it's actually a time in excel's view, so it's really the VBA code side of things I need. Thanks for any advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fields names do not show in excel 2007 pivot table fields list | New Users to Excel | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
How can I sort 4 fields at a time | New Users to Excel | |||
Adding Date/Time fields | New Users to Excel | |||
COUNTIF use with fields of time | Excel Worksheet Functions |