Approved by Tawny!
Okay, so I was typing up my work log for November, and decided I didn't like doing that. So, I spent a lot MORE work, making this calculator in Excel! And now I'm sharing it with everybody else in Belariath!
If you don't have Microsoft Office, you can use a Google Docs version HERE
So how's it work?
Here's what it looks like:
So as you can see, there are GREEN cells. These cells mean that you're supposed to put data in them. So, you type in your char's name, who employs them, their job position etc.
The RED cell you can see (B11 in the screenshot), is where the magic happens. Once your work log is completed, you click that cell, then copy the cell (ctrl+C is your friend), then go to your Work Log thread, and paste into where you would type your work log, and that's it! You have a fully formatted work log, split over the weeks, with your pay automatically calculated based on what hours you've worked!
Cool, so how do I use it?
First, let's break it down:
[1] Type in your character's name
[2] This is a dropdown, select where your character is employed from the list of all the places of employment
[3] Type in what your character is employed as (Clerk, Manager, Enchanter etc)
[4] Is your character marked as a Manager or Assistant Manager in the payroll? If so, select the appropriate option from the dropdown menu
[5] Once you're ready to post the work log, highlight this cell, copy, and paste into the MB! (Don't copy the formula, copy the cell itself)
[6] More dropdowns, the left column has all the ranked skills, and the right column lets you select what skill they're currently at.
[7] Here is where you post your times, more info below!
So each green cell expects an input of some kind. However, if you have no skills, you can leave the skills blank. Remember that only skills at Master rank give you a bonus to your paycheck.
How to add in your times
As you might already know, payroll is calculated with Desdaemona's !time command. When you use !time, Des gives you the following result:
<Desdaemona> Current time (as shown by Message Board) in UTC/GMT is: 2018-12-11 07:56:12 - please remember this is NOT game time
What you need to do, is copy the DATE and TIME from that message, so in the above example, you would copy "2018-12-11 07:56:12", then paste it into the appropriate field on the spreadsheet. (So, if 2018-12-11 07:56:12 was the starting time for a shift, I would paste the date and time into the "!time on start" column.
Here's an example:
Amethine worked at the Healing House as an Assistant Headmistress, and spent just over 2 hours there with 3 skills at Master rank. The shift started at (according to !time's from Des), "05/12/2018 20:34:45" and ended at "05/12/2018 22:37:08". This is how the calculator should look:
(You can see that I also am keeping track of my Umbara worklogs too, and off-screen to the right, is the Apothecary!)
Even though only the time is shown in the cell, you can see that the date is also in the cell. This is a quirk of Excel, where a date and time in the same cell only displays the time, but the date is still there too. Just hidden.
The next post in this thread, is what the above screenshot would look like as a worklog. So only the first week has any hours in it, and only 2, but I get 240mhl as I am an Assistant Headmistress, and another 150 for my skills.
Q&A
Q: There's only one row for entering times!
A: That's "Conditional Formatting". When you enter times into the first row, the next row turns green!
Q: I don't think it's calculating pay right
A: I did what I could to make it as accurate as possible, you get pays 10mhl per full half-hour worked, per week. If you think there's a discrepancy somewhere, respond or PM me and we can see what the issue is!
Q: I don't have Microsoft Excel
A: Click Here for a Google Docs version of the latest version, I took the liberty of uploading the calculator to it, and clicking this link should prompt you to make your own copy to do with as you please! I can't guarantee that it'll work flawlessly, but if there's any issues by all means let me know. Using popular alternatives isn't recommended, as they don't seem to work well with the downright horrible formulas I've used. Apache Open Office and Libre Office don't appear to work from what I've seen, but your mileage may vary.
Q: I don't see a place to put the month!
A: You already put in the month when you copy in the date! The calculator takes the month from the first starting time you give it, and makes that the month for the post. Change the date, and the output will change accordingly!
Q: I have another question!
A: Then ask! I'll try and keep this as updated as I can. The code's not pretty behind the scenes (traverse at your own risk), but it works!
VERSION HISTORY
- V 1.4.2
- Managers and Assistant Managers now show correct number of hours to be calculated at normal pay if worked for more than 4 hours that month.
- V 1.4.1
- Fixed some typos in the skills list.
- V 1.4
- Fixed the post creation formula's messing up occasionally in the 2nd and 3rd jobs.
- Added Gaea's Guzzler to the employer list
- Added the version number to the sheet itself
- V 1.3.5
- Fixed the calculator not properly adding in skills for second and third jobs.
- Tweaked how the calculator displays the current month, now you don't have to have the first cell of the first job filled in to see the current month.
- V 1.3.4
- Added Dethsiris as a place of employment.
- V 1.3.3
- There was an issue with the calculator not reading the correct month, this has now been fixed.
- Added a cell to display the current month of the worklog to make troubleshooting easier.
- V 1.3.2
- There was some issues with how the end times were being displayed in the forum output. This has now been fixed.
- There was a slight issue with the secondary and tertiary trackers in that they sometimes used the wrong base mhl/hr. This has now been fixed to properly use 20mhl/hr unless SS, REB, IG or ISA is selected.
- V 1.3.1
- Sometimes calculator wouldn't award Master rank skills properly in the forum output cell, this has been fixed.
- Added calculator version number to forum output cell.
- V 1.3
- Fixed an error that would award more mhl to Assistant Managers and Managers.
- Added protection to the sheet, so that only cells you're supposed to be able to select or change can be selected or changed. This is to help prevent errors caused by accidental deletion of "formula" cells. There's a lot of them.
- V 1.2
- Updated "total mhl earned" sections to properly display 0mhl if there has been no times added.
- V 1.1
- Added a "total mhl earned" field so that you can see at a glance how much each job is giving you for that month, as well as a running total for all jobs.
- Added a check so that if you accidentally put in the same profession more than once, the input fields turn red to indicate the error.
- V 1.0
- Release