Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How do I input data into 2 worksheets simultaneously.

0

Hello sorry Don it's me again. 

On the premium course you have explained how to input from an input form data to the next available free row on a specific spreadsheet in Module 2. Is it possible to write a code to input data onto another worksheet, within the same or another module, against a specific time zone, as opposed to the next available line, simultaneously from the input form. For example in my Workbook SOMS. I need to record application forms as they arrive using the ID number, but applicants will want to play at different times, so the "Entries" sheet the times are mixed up but they need to sorted into time slots ranging from 8.00 to 15.30 and these will be recorded on the "StartSheet". Each time has 2 entries e.g. 8.00 and 8.001, 8.09 and 8.091 etc. with the times formatted as numbers to show 2 decimal places only, this is to facilitate 2 teams of 2 playing together within the same time zone.   

Help is appreciated.  

Answer
Discuss

Discussion

Efficiency dictates that data should be recorded only once. The database such created can later be sorted to different rules or extracts be created on the fly. This is generally faster than maintaining permanent copies. I also point out that to create, use and display a number like 8.001, with the meaning of 1/1000th minute after 8 am, is problematic because 8 am has a time value of 0.33333333 days which can be converted to hours by multiplying with 24. 1/1000th minute equals 1/24/60/1000. There will never be a value like 8.00 except as the result of intricate calculations and the same holds true for 0.001 hours. Such calculations are far too cumbersome to be used on numbers whose prime duty it is, I presume, to enable the calculation of times lapsed.
If you ever need to calculate times across midnight I recommend to standardise on Day/Time values (like 43765.33333333), forgetting about hours and foresaking displays like 8.00 + 0.001. 43765.33333333 + (0.001/24) would do what you want but it won't necessarily display according to your desired logic unless you give up on a standard base for all time calculations which you will surely come to regret.
Variatus (rep: 4889) Feb 5, '19 at 9:07 pm
Hi. Thanks for the input Variates,  but truthfully what you are saying is as clear as mud. But that said I do understand where you are coming from. So to get around the problem I could use whole numbers only ie. 1,2,3,4,5,6 etc. With numbers 1&2 representing the 8 o'clock slot, 3&4 8.09 slot etc. 
Jim W (rep: 14) Feb 6, '19 at 8:31 am
Add to Discussion

Answers

0

Having trawled through the Forum and the listed examples  I have eventually found the Subject Automatically Copy new data to another worksheet example. I believe with a bit of work that this is what I am looking for, and hopefully with a bit of fiddling around I should get it to work eventually.  

Discuss
0

Excel cells have many poperties, among them the Formula, the Value and the NumberFormat. For example, if you enter =TODAY() in a cell Excel will know that it's a formula by the leading = sign and assign the string to the Formula property automatically. Then Excel will evaluate the formula and assign the result to the same cell's Value property. In this case, today, that is 43503. While Excel evaluates the TODAY() function it learns that the result must be date. Therefore a NumberFormat like mm/dd/yyyy will be set (depending upon your computer's Internationl settings), also automatically, and you may see 02/07/2019 displayed in the cell.

You can reverse the automation in each step. You might set a NumberFormat like dddd and display Thursday in place of the date. Or set the NumberFormat to General and see  43503. You might precede the formula's = sign with an apostroph, like '=TODAY(), and thereby cause the formula to become a text string which Excel will assign to the Value property and no longer evaluate.

Today is the 43503rd day since Jan 1, 1900. It follows that tomorrow will be represented by number 43504 and yesterday was 43502. =TODAY()+10 returns 43513 which can be displayed as whatever the NumberFormat property demands, and that is the entire "secret" of calculating dates in Excel.

Since one day is represented by the integer 1, it would follow that 0.5 should be half a day. In order to instill this notion with sense MS engineers determined that day 43503 should start at 00:00 and end at 24:00. Therefore any fraction added to 43503 must represent a time during that day. Logically, 43503.5 should be 12 noon. And it is.

Accordingly, if you apply a NumberFormat like hh:mm:ss to the cell containing the =TODAY() formula you will see 00:00:00 but =TODAY() + 0.5 would display 00:12:00. Try =TODAY() + 0.75 would display 00:18:00 or 00:06:00PM. If you have =TODAY() in A1 and B1 =A1 the two cells could have the same Value but display different things. You can also construct Date/Time NumberFormat like mm/dd/yy hh:mm:ss.

Since the interger 1 = 1 day, and one day = 24 hours, 1 hour must be 1 / 24. 1/60th of that (1 / 24 / 60) = one minute, and so on. Excel applies the conversions as part of the NumberFormat. You don't need to know or care how many threes there are in eight hours. The challenge you face is focused on data input. I have suggested that you use date/time numbers for all your times throughout your project. Therefore every time you enter must be converted to its appropriate date/time value at the point of entry.

Actually, Excel makes this task easy, again employing the NumberFormat. A cell formatted with hh:mm:ss will be assigned the correct Value automatically if you enter 8:00 or 8:00:00. Use Data Validation to reject entries like 8 or 8.00. Find a way (using hidden columns, perhaps, or VBA) to add the date to the user's entry.

Discuss

Discussion

Variatus, Thats very good of you to explain the time function in Excel and how it works everything out. Thank you
Jim W (rep: 14) Feb 7, '19 at 3:21 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login