[Dropped] I'd like to build this Timesheet in Basic code

[rickwesh]

Hello everyone.
I'm new here to the forum.
I created this originally in Excel, then in OpenOffice. This version is in LibreOffice.
I would like to create some module code to build this from the information placed on the WRK sheet.

Currently I have a simple solution for the overtime calculation, how should I change it, if there is someone that thinks it should be changed.
Each of the cells on the YTD sheet I originally setup as hyperlinks to the sheet in Excel, but I can't do that in OO or in LO.

In essence I would like to be able to start the year with nothing but one sheet that contains the pertinent information, and some macro controlled buttons to build the sheet from there.

I welcome your thought's on the worksheet as well as ideas/pointers on doing the coding.

It has been a very long time since I've done any programming. Say since volume 12#7 of PC-Magazine.

Thank you so much for your time.

TimeSheets.ods

Year long timesheet setup, one person straight time

(37.36 KiB) Downloaded 79 times

[JeJe]

If you make a start and then when you get stuck you can ask for help. You can find the OpenOffice.org 3.2 BASIC Guide online and
Andrew Pitonyak OpenOffice books too.

The best way though is possibly to do a search here or in a search engine for what you want to do eg for

openoffice macro how to set a cell value

And look at the result - which may well be from here. And work like that...

[rickwesh]

Thank you for the reply.
I actually have started some coding. As a matter of fact I strayed up considerably longer than I should have last night.
I have the guides, as well as the PDF by Pitonyak.
Restarting coding has gone pretty smoothly considering how long it's been since I last took on such an endeavor.

The reason for my posting the workbook here was in hopes of getting comments on the layout of the spreadsheets.
I created the workbook for personal use.
I know the idea of a timesheet workbook is probably mundane, but could you give your thoughts on what I produced?
Thank you again.

[Villeroy]

The document is complete rubbish. You ignore the entire software. Before adding even more stupid Basic code to this nonsense, you should learn how to work with spreadsheets, office documents, templates, styles in general.
Every IT professional would implement anything like that with no spreadsheet at all. Collecting data in constantly growing lists is a database task, no matter how many spreadsheet users try to do this with spreadsheets. Spreadsheets are the most popular misapprehension in the history of software.

[rickwesh]

Ok well thank you so much for your time and thoughts.
Please have a wonderful day.

[MrProgrammer]

I welcome your thought's on the worksheet as well as ideas/pointers on doing the coding.

You could save a couple of weeks of your time by not using macros. You can get essentially the same information with a simple data sheet and two pivot tables, one for month summary and one for year summary. For 2024, make a copy of the spreadsheet, add 365 to the dates in Data.A2:A366 with Edit → Paste Special → Add and fill down another row because it's leap year. Even better, keep everything in one spreadsheet and add year filters to the pivot tables.

A pivot table provides minimal formatting options however you can copy all of its cells via formulas =PivotTableCell and format the copies to death with colors, fonts, bold, custom headings etc. This is best done with styles.

I welcome your thought's, rickwesh, on the spreadsheet which I've attached.

202304232058.ods

(36.66 KiB) Downloaded 70 times

It has been a very long time since I've done any programming.

That won't be the main problem. It will be understanding the UNO API. It is quite clever, but the concepts are hard. Expect to spend at least a week to learn the essential ideas of services, interfaces, etc. Then expect to spend at least another week to determine which of the thousands of services, interfaces, methods, and properties will provide the data you want and allow you to manipulate it. This is really complex stuff. There are many examples for you in the forum, but finding the appropriate ones for your task and understanding how they work will be a slow process.

I do not offer to provide any macro assistance or examples.

Each of the cells on the YTD sheet I originally setup as hyperlinks to the sheet in Excel, but I can't do that in OO or in LO.

In the Year pivot table, enable Drill to Details. I haven't used this much but I think it might do what you want. Use the Help button in the dialog to learn more.

Currently I have a simple solution for the overtime calculation, how should I change it, if there is someone that thinks it should be changed.

Your formula =IF(cell>8;cell-8;) produces Err:511 in OpenOffice. I changed it to =MAX(cell-8/24;0), keeping track of time in days. Read section 4. Times in cells in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[karolus]

The document is complete rubbish.

+1
Yes indeed! But at least it's really nice and colourful!