Calendar from Calc?

English
#1

Hello

I’ve been given a spreadsheet with days of the month (columns) and 2.5 hour timeslots split with 8 possible areas under each (rows). The times overlap a bit (e.g. 00:00-02:30 and 02:00-04:30)

The areas are represented by numbers 1 to 16 randomly.

What I need to do is extract all the occurrences of a particular number (in my case 14 and 16) and create a calendar entry per occurrence from that to use in a Google calendar. The month and year for entries can be November 2023 since it repeats the same each month.

Is this possible without having to do it manually?

#2

An uploaded document with sample data tells more than thousand words.

2 Likes
#3

Maybe you could use a filter and extract output to a second sheet.

It is possible to create/write .ics-files, with a bit work. I guess you can import them to Google calendar.

#4

I can’t find an upload option for the file here (maybe because I’m a newbie still) but here is a link to it http://www.s4s.co.za/documents/ls/CP-LS.xls

#5

So I really have no idea about filters. I’ll have to look into it.
Spreadsheets aren’t my day to day thing - that’s Corel and Quickbooks.

What about CSV file with all the entries that would upload to Google calendar?

This is the file by the way
http://www.s4s.co.za/documents/ls/CP-LS.xls

#6

Any reasonable filter or sorting requires normalized data. Your spreadsheet is a cross table (31x96 values at crossing of row labels and column labels).
The second sheet of the attached spreadsheet document converts the cross table into a normalized one with auto-filter buttons.

Drop down the filter button in cell H1, deselect “All”, select 14 and 16.
Then select the resulting list in columns D through H and paste-special (Ctrl+Shift+V) valu
es+formats to some other sheet or document in order to get rid of the formulas.
topic97867.ods (131.2 KB)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.