This project can/may lead into another project, if Coder/Buyer work well together and product mutual satisfactory results together, that is a bit more complicated related to Networking (IP Address, MAC Addresses, Networks, Routing, VLANs, etc. - data within a few spreadsheets that need work to pull exactly what I am looking for.
For this project, I have Excel Questions (formulas/samples/training):
I am using Microsoft Office Excel 2007, and I need assistance/training on creating formulas, calculations, etc. to get and obtain data information from existing spreadsheets/data that I have either collected or created. I would like to learn (or if necessary, create from scratch/start) how to do the following:
A Timecard Tracking Spreadsheet:
I would like to create a timecard to track and report on my non-normal work hours (dates/times/totals/owe employer/owe me/etc).
References and Columns:
Inserted days, times, breaks (15min or 30min or 60min) - but place one time only in spreadsheet as reference point...
Normal work days, examples Mon-Fri or Mon-Thur
Normal work hours, examples 8am-5pm or 8am to 7pm
Normal break periods (break 1 @ 9am, break 2 @ 1pm, break 3 @ 4pm)
(use these days/times as standard template data for rest of spreadsheet and formulas, in case my schedule shifts, I just have to enter it one time and the spreadsheet will use the adjusted days and times as its "normal schedule" reference
Line Item, Date(s), Weekday, Start Time (usually AM, sometimes PM), End Time (usually PM, sometimes AM)
Reason for work hours change (medical, personal, etc.), the problem/why worked less or more, resolved/how fixed the problem, other/reason like met with manufacturer after hours
Formulas and Reporting seeking help with creating (what I will expect coder/programmer to do and show me):
1.
Calculate the hour(s) difference between start and end times (example, 8am start, 5pm end = 9hrs unless lunch taken, then 8hrs)
2.
If "over" Midnight, into next day, auto calculate same (example, start 5pm, end 5am = 12hrs overnight to next day)
3.
If inside normal work hours (and I arrive late, leave early) - I owe employer
4.
If outside normal work hours (start early, work late, nights, weekends, holidays) - Employer owes me
5.
Auto sum/total the columns of hours owed to employer, hours owed to me, reason for shift change summary (like medical related 10 times, personal related 5 times, etc.), and calculate difference and display who is "owed" the appropriate amount of time, as of spreadsheet completion/print date
6.
Also, would like to be able to report on "how many times I started early, worked late, worked nights, worked weekends, worked holidays, worked back to back shifts, worked over 24hrs)
7.
Any fields "not" containing input/data, that need to remain empty/no content, to do so automatically (no text, no number, no formulas, no errors, etc.)
8.
Finally, if any data can be auto calculated/determined, so I dont have to enter it manually, I would like that feature enabled/added (an example, if I type in a specific date (say November 1, 2013 - the "Day" column maybe could auto populate "Friday") or perhaps the "line item" field, if I type in a Date - then that field auto populates its next number, like if number 1-8 exists, when I insert a date, number 9 gets added automatically - otherwise, blank/empty.