help with complicated formula

[crasherpaul]

Hi everyone


i need some help with my worksheet, ive scoured many youtube videos and other sites to help but to no avail. i have attached my worksheet for people to take a look at to try and get a grasp of what im on about.


i need some formulas to work out time worked and it needs to incorperate overtime and if someone has a sick day, holiday or just a day off. i need this to be one formula, i also have a section that need to calc working day eg 13 or 15 so if someone works >=13hrs then the box needs to show 13hrs and if 13hrs and 01min it need to show 15. also i need a warning of some sort either by changing the cell colour to red or have pop up to day that if they use a 15hr then they have 2 more 15's left and if they use another the needs to say 1 more left and if last one is used then zero left. this need to calc per weeks. similarly for the driving hours. a driver may extend only twice per week.

oh and when holiday is entered it only pays 8 hours. all info is entered on the userform.

could someone help as im getting desperate now.


many thanks
Paul

[SamT]

May I suggest that your project will be easier to code if you use the Annual sheet to record all the information found on both it and the pay sheets on a worked day by worked day basis. The code can fill in the missed dates with "Day Off" on the pay sheets. Do not place any formulas on the sheets, let code do the work.

That you consider the pay sheets as merely reporting data from the Annual sheet and only the data since the last pay day, and the year to date totals.

That you do not give the drivers a way to acess the Setup sheets, or to enter data from a date earlier than the last date on the annual sheet. for example, when they "forget' to enter a days work report. Leave those tasks for the office personnel.

I would also make the Setup and Annual sheets xlVeryHidden and only allow office personnel to even view them.

I noticed that your upload has a lot of code that is not used. In the future, please delete any such code or forms when you upload examples.

[crasherpaul]

the reason im doing all this is because as a driver myself i want to know exactly what im gonna get paid, because if the office makes a mistake with the wage i will know exactly how much they have short changed me by.

as for making the Setup sheet being very hidden i will be doing that before giving out this workbook to other drivers.

however i still need help on this worksheet please

May I suggest that your project will be easier to code if you use the Annual sheet to record all the information found on both it and the pay sheets on a worked day by worked day basis. The code can fill in the missed dates with "Day Off" on the pay sheets. Do not place any formulas on the sheets, let code do the work.

i would like to see your suggestion on this as i really dont know what you mean

[SamT]

If this is a Drivers workbook, then the above comments about the office don't apply,as the driver is the office

I took a short look at your book. Please give me a little time to think about it, as I did see some issues (for example; many input boxes that probably have the same date in them,) and I might have some more suggestions to improve the UserForms.

The work you have put into this is really impressive.

What I meant by using the Annual sheet as a database was that every time the Save button on any form is clicked, all the data from all the Forms' input boxes be saved to the database sheet.

The data inputs to the Pay To Date sheet should be retrieved from the database sheet as needed. The formulas for Projected pay can be on the sheet. If you have a means for the driver to enter actual pay, that data should be stored on the DB sheet when saved.

The schedule sheet is obviously a special case, since the driver can only input future schedules as he learns them, and it needs to pull past data from the DB sheet, and it probably be easiest to have some formulas on it. As I see it, it only needs to show date from the last few days and for any future days where the schedule is known. VBA code can scroll these rows for you.

[crasherpaul]

SamT please let me say thank you for quite a comprehensive reply i was not expecting anything like that . i would be very interested in seeing your ideas, what i would like to do is upload the workbook again since i have done some changes to the workbook and have many ideas that i want to apply to the worksheet. i never realised that this was going to get so involved but as a driver myself i want to make sure that other drivers get the best from this and not have it being some very simple time sheet.

there is also this idea that im after doing as well

i have a cell N3 where i put lets say Aug13 and i have cell that hopefully put in 1 aug 2013, what im after is this.


if N3 is August or aug13 then i need either 29 or 30 rows created and filled with the number of days in the month, the reason for this is that there are 30 or 31 days in a month as you know.


is this possible?

i really would love to collaborate with you on this. i am planning on selling this for about £10 per copy and if this goes well would love to share it with you. sorry to admin if this line is wrong.


look forward to hearing from you soon

my per email is flyinghigher2011@gmail.com

[crasherpaul]

i also came across this little sheet that i would love to incorporate as well. with this they could scroll through past months and if needed have the ability to print it.

[SamT]

I'll check both of them out.

This code always returns the number of days in the current month. The way it works is that the zeroth day of next month is the last day of this month.

Sub test()
Dim X
X = DaysInMonth
X = DaysInMonth("Nov")
End Sub

Function DaysInMonth(Optional MnthStr As String) As Long
'Reurns days in current month if MnthStr Is missing

Dim Mnth As Long
If MnthStr = "" Then
  Mnth = Month(Now)
Else
  Mnth = Month("1/" & MnthStr & "/2000")
End If

DaysInMonth = Day(DateSerial(Year(Now), Mnth + 1, 0))
End Function

[SamT]

I think you're talking about the Payslip sheet in the EPIF book.

A little bit about nomenclatures and definitions I use. Databases (In Excel) are tables with labels in Row 1 and/or each column is a Defined name range.

Reports are temprary and contemporaneous, that is they are designed to be a report of information about a specific time and are to be viewed or printed, but do not store any information. They are analogous to paper forms and are usually designed around an existing paper form. They can be on a worksheet or on a UserForm.

Forms are always UserForms. So if I were to say "you need a report for that" you can use a worksheet or a UserForm, but if i said "You need a Form," I mean a UserForm only. Forms are used to enter or edit or occasionally, view, (like a report,) records in a database.

UserForms only print as images, so it is usually best to use Worksheet reports for printing.

For now, hold off on designing any more forms or reports. The very first thing to design in any middling to large app is the database, because

Function Follows Data Structure

I'm attaching a list of all the input boxes on the main form. I leave it to you to understand what data they represent. You will need these to name the columns in the Database sheet. Just put the data names in the top Row as column labels. The Data names should be understandable by any trucker.

When done, the database sheet should have a column for each different piece of data. Don't worry if the table seems to be getting too wide, it's not really for humans to look at anyway. That's what Reports are for. Now I know that I said not to design any reports right now, but you do need to take a good look at all reports desired to make sure there is a data column for each bit on the reports.

Since you only have around 90 controls on the Form, I don't think that we'll run out of columns, even in my Excel 2002 version, so go ahead and group the columns with empty columns between groups, if you like.

One thing, the first column must be for the DATE of the entries. That is the main lookup for all entries, even though we'll be doing lookups on other columns, too.

[SamT]

Paul,

Since I have done this before I started a database driven layout for you.

After you have gotten all the DB column Names, fill out the vbaLists sheet as far as they go.

When that is done, I will make new names for the form controls according to a formula that makes coding much simpler. Then I'll give you a list of the changes so you can do all the renaming of the controls.

BTW, on vbaLists, the columns of "Column Numbers" should be continuous, but leave blank cells in the "Column Names" and "Control Names" columns to represent empty columns on the DB sheets. The purpose of this is to make it very easy to modify the DBs and UserForms.

[crasherpaul]

i had issues with formulas and links due to the fact that this workbook was edited in office 2003 and im working with 2010 not sure how to get around it. also i make some notes that you may be interested in.

[crasherpaul]

ignore the workbook but please take a look at my redesigned userform

[crasherpaul]

SamT id like you to take a look at the full driver time sheet and you will see everything that i have on it. inc my databases. let me know what you think.

[SamT]

Paul,

I think that you still don't understand the differences between Data Bases and Reports.

I know... That anybody who tries to use one table for both purposes is doomed to failure. To use a vehicular analogy, they will have to pull the engine to change the oil and step out of the cab to set a turn signal.



Let me offer you this thought experiment. A flight of fancy, if you will: You find a young man with lots of promise and take him on as an apprentice driver. You try to teach him the best routes, the best places to over night, and all the things a successful driver should know. All day long all this kid does is tell you how he would do things and expect your approval about those things.

That's the way I'm beginning to think about you. You're the kid.

Don't get me wrong, Paul. You have lots of potential, your UserForm shows that you have a very good sense of detail, you are obviously very enthusiastic about this project, and you even have something I don't; a good sense of esthetics.

Now I might be a grumpy old man who doesn't know his arse from the tailpipe of a 7.5T lorry, but I've been driving this application building process for a lot longer than you, and if you really want to make a good, easily usable and easily maintained Driver Log, you're going to have to listen to me.

I know that It doesn't seem like my previous hints are going anywhere usable and that you really can not see the light at the end of that particular process tunnel. I am not going to put the entire development process into one post. But if you do the things I ask as I ask them, you will get to a usable product much faster.



One last chance.

Paul, there is one step I haven't mentioned yet and it is the very most important step of all. I will bet you a Pound against a biscuit that you haven't done it either.

Write down on paper, so you can see it often, in 30 words or less, exactly what you want this application do accomplish. I've done it for your App and I did it in 22 words. Don't count the first 3 words: "This Application will ..."

Obviously, you won't get into any detail in 30 words, but that is the idea. You need an overall goal.

Paul, get back to me when you've got that description ready.

[crasherpaul]

This Application Will........track wages, Nights out, expenses, working day, Holidays, enable editing of certain details. work history, view vehicle details with dates, hours and overall pay. (24 words) i think that about cover is.

[SamT]

Details. Don't want no stinkin details.

How about

This Application will give drivers an easy way to record and view all the information that they need to know in a well organized fashion.

[SamT]

i would be interested in seeing your thoughts on the time-sheet but there are some thing the i saw on the last sheet you sent over that i would not need which i mentioned. however i would have one question and that you would be this: would you need to change the layout of my userform or is it ok. see i did/do like the idea of being able to view history(s) of pay, night out history etc. and also have the ability to change a night out location if things change.

as you rightly stated before i am new to this and yes i did try to add alot of features in this to try to help the not just myself but other driver. my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me. im really sorry SamT

Oh yeah, beginners enthusiasm. I remember it well.

i am will and trying to learn but my head spins with all these ideas that i have and everything just seems to get away from me

The only way I have found to deal with these idea is to write them down. Go back to the last book I uploaded, Truck Driver Report 02.xls, and insert a new sheet behind the Control Panel sheet. Name it "Ideas." Stretch Column "B" to the Right until just enough of Column "C" is showing to act as a border. Format Column "B" with "Wrap Text." In Column "A" write a brief, one line, description of the idea. In the next Row, column "B," write as much about the idea as you need to. Skip a row and repeat with the next idea. I still use this process when developing an App.

my over all though was to show the userform only and not the worksheet. so they double click the worksheet but they only ever see the user form and not the worksheet unless they click a button to show work sheet from the user form (your thoughts on this please.)

In my mind, I see the App as an integrated object, where the user can see all the relevant sheets at will. I would have the workbook open to the Control Panel sheet, from which he can either open a form, or browse the sheets. I would only let him open a form from the control panel. This reinforces the idea that the CP is the way to edit the sheets and provides a consistent User Interface. A consistent navigation system is important to a well designed App.

IIRC, I haven't spoken of your current UserForm yet. That is because it is still too early in the App design process to put any time in thinking about the Form itself. I think of the current version of the UserForm as just another "Idea" Sheet. It's sometimes handy as shown by the ease of my getting a list of Data Points for you, but not necessary.

Briefly, these are the steps that IMO are required to develop an App like yours:

I: Develop the database

1. Create a list of every possible data point you can think of. See my "Company Database" sheet and your "VDO" Sheet. Name this sheet "Data Points List"
   
   • Use two columns, the first for the name of the data point and the second for a brief description of it. The second is mostly for me, but you will find it useful, too.
   • The Data Point "Date" should be at the top of the list.
     
     • Its description is "The Calendar date of which all records are pertinent to."
     • It is the only Data Point (DP) with the simple name "Date." All other Date names should be uniquely descriptive such as "VDOPrintDate," "ShiftStartDate," ShiftEndDate." etc. I'm not saying that you will have ShiftDates, that's just an example.
     
     
   • Using the description column, insure there are no duplicate data points. Indicate in the description which different place this DP is used.
   
   
2. Using a different sheet for each group, organize the DPs into related groups like the "Company Database" and "VDO" sheets.
   
   • Name each sheet according to its group
   • Put the lists in row 5 or 6 on each DP group sheet.
   • Using the Descriptions, make sure that each group sheet has its own copy of the relevant duplicated DP.
     
     • DP name (calendar) "Date" should be at the top of each groups sheet's list.
     
     
   
   
3. Review all the groups to insure that the requirements in steps 1 and 2 are met.
4. On each Group sheet, copy the list of Names and in cell "A1" PasteSpecial >>Transpose.
5. From now on, we will no longer refer to DPs and Groups, They have now transformed in to Field Names and Database Tables.
6. Format each Date and Time Field (Entire Column) to suit.
7. Save the workbook with the name "Database Descriptions."
   
   • Anytime in the future when you modify a database, edit this workbook to match, adding notes to describe what, when, why.
   • Keep Data Points List up to date.
   
   

Normally I would allocate several days for all the above, but you (and I helped a little bit with the list of DP Names,) have already done most of it. By the time you complete the above, you will have a very good feel for what your App can and should provide any truck driver and most important, what Form(s) you will need. The completed "Database Descriptions" is half the work of developing the UserForm(s), so do not think that it is not necessary.

Each of the below major steps will also take a few days. Only a few because of all the preparatory work done in step I. As you get to each step below, I will be expanding the minor step lists, so you will know what to expect.

II: Lay out the Userform(s)

• Save book as "TDR.v.01." Suggested name.

1. Use the Data Points List to know what controls you'll need.
   
   • More info later
   
   
2. Open "Database Descriptions" and add the sheet "vbaList" to the end.
   
   • Delete all DP lists, leaving only Field Names.
   • Complete "vbaLists."
   • Make Lists of Suggested Form Control Names
   • Add database type code to Database sheets and "vbaLists."
     
     • I already have this code, that only needs localization to work in any DB type sheet.
     
     
   
   

III: Code the Form(s)

1. Increment the Version number to .02

IV: Add Reports Sheets, Increment Version number
V: Repeat II, III, & IV as needed.
VI: Beta test.
VII: Repeat step V as needed.
VIII: Sell the Application
IX: Repeat Step VII as needed

You will have noted that with each major step, the work book gets saved with a new name. It is not until the end of step V that it actually gets a working name. Be sure and keep a copy of each workbook with a different name, even if it's just a minor revision number change.

I usually keep my projects in their own folder with the following sub-folders:

• bas
• Archives
• Helps
• Examples

bas is where I export to and import from any modules I want to move from book to book.

Paul, take whatever time you need to understand what is in this post. As questions about it if you need to, but really try to fully understand it.

[crasherpaul]

SamT i made a start on the workbook but did not want to go to far only to find that i had not followed your instructions properly. I only ask one thing SamT and that to remember that i am a complete novice at this and will make mistakes, i do understand that you are an expert and pritty much know everything about designing things like this where as i am not so please be patient and i only have around 3 weeks left to complete the thing and putting in around 10 to 12 hours on this every day.

i have made a few changes to the userform as well to take into account different things i need.

thank you for your patients

[SamT]

I have done about half the work needed on the Databases. Your form is so hard to use and understand. and your abbreviations so opaque that I could not finish them.

All that is really left for you to do on the DBs is figure out the names that I couldn't and fill in the column Labels and the DB Descriptor tables, (those are the three-column lists I put in row 5 or 6, column "A", and below on each DB sheet. Leave those lists there! Note that the name of this workbook is DB Design! It is a permanent record of the DB layouts.

Note that the control names and the Column Names should closely resemble each other. Try not to use abbreviations. New drivers might not understand them, I certainly won't, and they make coding harder. Obviously, a well known abbreviation is okay, (ODO for odometer, Rcvd for received, etc.) Look at all the examples I provide on the DB sheets. (DB for database)

Note that the workbook is an xls type book. Always try to save all your coding and design and development work in older style books so that you can sell your product to drivers who are still using older versions of Excel. I recommend saving all books as Excel Version 2000 if you can. All the books I send you will be in version 2002, which will probably be good enough, because I learned with version 97 and tend to stay with that code base. IF the book still works when you open it after saving it as version 2000, you know that it will work in that version (2000) of Excel.

Any time you are waiting on a response from me about the DB Design book, you can start on a Reports Design book. Only design Sheet style reports for now and do not worry about getting the data on the report. Just put the DB and column Name in the blanks. A real example from the DB Design book,

TachoDB!ODO Start

Note the Exclamation mark after the DB Name. The code will find the appropriate date on the DB using the ShiftDate Column (column 2).

[crasherpaul]

Hi SamT

I think that I've done all that you asked! could you please take a look and tell me what you think.

Thank You


unfortunately i don't know how to delete previously uploaded files and cant upload any more so I cant upload this amended one. All I can do is give dropbox link.

https://www.dropbox.com/s/gamnv5fnps...0DB%20v.06.zip

[SamT]

I'm on it