TLI Worklog Calculator! Auto-generate your work logs.

Useful items for all visitors - virus warnings, firewalls, IRC scripts etc

Moderators: Ehlanna, Twerlinger

TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Tue Dec 11, 2018 8:28 am

Introducing...

Amethine's TLI Worklog Calculator V1.4.2!
Approved by Tawny!

TLI Worklog calculator V.1.4.2.xlsx
(162.5 KiB) Downloaded 33 times

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:

Image


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:

Image

[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:

Image
(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

TLI Worklog calculator V.1.4.2.xlsx
(162.5 KiB) Downloaded 33 times
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Tue Dec 11, 2018 8:29 am

Below is what the red cell gives you if you paste it into the MB, using the screenshot in the first post as an example

"Character: Amethine Tawariell
Month: December
Healing House Position: Assistant Headmistress
Applicable Skills: Alchemy: Master | First Aid/Triage: Master | Tending/Etiquette: Master

WEEK 1 (1st to 7th)


Start Time: <Desdaemona> Current time (as shown by Message Board) in UTC/GMT is: 2018-12-05 20:34:45 - please remember this is NOT game time
Finish Time: <Desdaemona> Current time (as shown by Message Board) in UTC/GMT is: 2018-12-05 22:37:08 - please remember this is NOT game time

Worked with Shievera and Lianna to prepare Rhezha(Kitten)'s body for rezzing, contacted both ISA and IG as Kitten is currently enslaved.

Hours Total: 02:02

- - - - - - - - - - Total Payable Hours for Week 1: 02:02



WEEK 2 (8th to 14th)


- - - - - - - - - - Total Payable Hours for Week 2: 0:00



WEEK 3 (15th to 21st)


- - - - - - - - - - Total Payable Hours for Week 3: 0:00



WEEK 4 (22nd to 31st)


- - - - - - - - - - Total Payable Hours for Week 4: 0:00

- - - - - - - - - -

Total payable hours for December: 02:02

- - - - - - - - - -

Payment = Assistant Manager Pay @ 240mhl for first 4hrs of month worked + skills at Master Rank: Alchemy, First Aid/Triage, Tending/Etiquette,

- - - - - - - - - -

Total amount due for December: 390mhl



Calculated with Amethine's little calculater!"
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Ehlanna on Tue Dec 11, 2018 5:44 pm

From what I can see, if you download the googedocs version as open document (.ods) format it will load and run fine in Libre Office.
User avatar
Ehlanna
High Council
High Council
 
Posts: 2494
Joined: Sat Apr 09, 2005 9:23 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Farvel on Wed Dec 12, 2018 7:10 pm

Very interesting.

I haven't tested it as I'm not on my laptop at the moment. I did notice a few issues from your post, though:

1) Some shops (currently I believe the SS, REB and ISA) pay 30 mhl per hour insteaf of 20.

2) Managers/Assistants get paid 240 mhl for their first 4 hours per month, not week, then regular payment after that. But I believe they have to put in those 4 hours to claim the bonus payment. So in your example you only put in 2 hours so would not get the bonus.

I could be wrong, though.
Image
User avatar
Farvel
Predominate
Predominate
 
Posts: 366
Joined: Sat Dec 21, 2013 3:16 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Wed Dec 12, 2018 10:10 pm

Farvel wrote:1) Some shops (currently I believe the SS, REB and ISA) pay 30 mhl per hour insteaf of 20.


That's already accounted for, if the "Employer Field" is selected to be the IG, ISA, REB or SS, then the pay is calculated at 30mhl/hr

Farvel wrote:2) Managers/Assistants get paid 240 mhl for their first 4 hours per month, not week, then regular payment after that. But I believe they have to put in those 4 hours to claim the bonus payment. So in your example you only put in 2 hours so would not get the bonus.


I spoke to Tawny about that already, and she said that they'll still be paid their 240/325 for Assist Manager/Manager times, even if they only work 1 hour that week, so long as it's not a recurring thing. But that's a check for Tawny, not my calculator so currently they'll still get their pay even if they've technically not worked the full 4 hours.
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Gabriel Storm-Dancer on Wed Dec 12, 2018 11:38 pm

Are things like Seneschal, War Seneschal, and other chosen titles for those of noble house rank counted among Managers and Assistant Managers?
Gabriel Storm-Dancer
Expert
Expert
 
Posts: 169
Joined: Tue Oct 02, 2018 5:13 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Thu Dec 13, 2018 12:10 am

Stormdragon wrote:Are things like Seneschal, War Seneschal, and other chosen titles for those of noble house rank counted among Managers and Assistant Managers?


That I'm not sure about, you'd have to confirm with someone in the know about that, but once you do know it's a simple enough case of just selecting from the dropdown if you're to get assist manager/manager pay
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Farvel on Thu Dec 13, 2018 1:42 am

Amethine wrote:
Farvel wrote:1) Some shops (currently I believe the SS, REB and ISA) pay 30 mhl per hour insteaf of 20.


That's already accounted for, if the "Employer Field" is selected to be the IG, ISA, REB or SS, then the pay is calculated at 30mhl/hr

Farvel wrote:2) Managers/Assistants get paid 240 mhl for their first 4 hours per month, not week, then regular payment after that. But I believe they have to put in those 4 hours to claim the bonus payment. So in your example you only put in 2 hours so would not get the bonus.


I spoke to Tawny about that already, and she said that they'll still be paid their 240/325 for Assist Manager/Manager times, even if they only work 1 hour that week, so long as it's not a recurring thing. But that's a check for Tawny, not my calculator so currently they'll still get their pay even if they've technically not worked the full 4 hours.


Ok.
Image
User avatar
Farvel
Predominate
Predominate
 
Posts: 366
Joined: Sat Dec 21, 2013 3:16 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Akaton on Thu Dec 13, 2018 2:24 am

Wait managers get 325? Shit I thought it was 320. Ive been doing all my work logs wrong. Ive been undercutting myself a little bit each month. But to answer Gabriels question yes Seneschal and War Seneschal are effectively managers. And they can hire assistant Managers under them under no real title other then what they are called within th house itself.
User avatar
Akaton
Expert
Expert
 
Posts: 208
Joined: Thu Sep 06, 2012 5:13 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Thu Dec 13, 2018 3:50 am

Akaton wrote:Wait managers get 325? Shit I thought it was 320. Ive been doing all my work logs wrong. Ive been undercutting myself a little bit each month.


Apologies, mistyped. It's definitely 320 lol.
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Tawny on Thu Dec 13, 2018 4:57 am

I need to address one thing here. There is only one manager and one assistant manager position per employment. I doubled checked with L not too long ago and she confirmed that. So each payroll can only have one Manager and one Assistant Manager. The rest on the payroll make the normal hourly wage.
Image
User avatar
Tawny
Moderator
Moderator
 
Posts: 696
Joined: Mon Mar 05, 2007 2:16 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Gabriel Storm-Dancer on Thu Dec 13, 2018 9:37 pm

So again, in respect to Noble Houses, how does that work in regards to Seneschal and War Seneschal? I guess Sen would be manager and war sen be assistant manager? I dunno tho.
Gabriel Storm-Dancer
Expert
Expert
 
Posts: 169
Joined: Tue Oct 02, 2018 5:13 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Amethine on Thu Dec 20, 2018 4:48 am

CALCULATOR UPDATE

Updated Calculator to version 1.1

The following has been added:
  • Running total of mhl earned for each job, along with a total for all jobs earned so you can see what you'll be getting when payroll comes.
  • Selecting the same professions more than once for the same job highlights those fields in red to inform you of the error

The title post has been updated with the new calculator version ready for downloading. If you copy over the times from the old sheet, please right-click and paste as values, as pasting normally tends to keep the formatting and that'll make things look ugly.

If there are any problems lemme know!

-Amethine
Amethine Tawariell is the main catgirl.
Felicia`Delacroix is the naughty catgirl.
All the catgirl's just wanna have fun!

If you like this wax-seal, I could make one for your too. PM or hit me up in IRC if you're interested :)
Thank you Abby for drawing the amazing pic I'm using as the avatar!
User avatar
Amethine
Predominate
Predominate
 
Posts: 253
Joined: Fri Apr 20, 2018 5:40 pm

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby Ellyssa_Flamewing on Mon Jan 21, 2019 9:19 pm

I've tried it for myself and I like it. It caught an error or two in my own book-keeping.
User avatar
Ellyssa_Flamewing
Expert
Expert
 
Posts: 241
Joined: Tue Apr 08, 2014 4:01 am

Re: TLI Worklog Calculator! Auto-generate your work logs.

Postby AlthiaReveluv on Fri Feb 01, 2019 3:16 am

As a novice player here i have to say this calculator is amazing! Thank you so much Amethine! i was worried about posting my work logs and this helps immensely. =D>
User avatar
AlthiaReveluv
Novice
Novice
 
Posts: 18
Joined: Wed Jan 09, 2019 8:08 pm

Next

Return to Public Service

Who is online

Users browsing this forum: No registered users and 4 guests