copyright (c) 1998 by Juergen Galupki D-53757 Sankt Augustin GERMANY http://galupki.de Important note: Please forgive me my terrible english, i was born and live in germany (near Bonn) and so i speak and write much better in german language... The spreadsheet calculates the planned and actual Changes please only at the faces set aside gray monthly working hours appropriate split in the excel version ! up to several projects. Copy monthly and work with copy! Necessary: 1. Work time begin and end, daily break C1:C7 ... Work time begin monday - sunday e.g.: '08:00 eight'o clock in the morning D1:D7 ... Work time end monday - sunday e.g.: '16:30 (in 24 hours format) H2 ... Duration of break e.g.: '00:30 for half an hour I2 ... Duration of working time after which a break MUST be made! e.g.: '06:00 after 6 hours The information is used to determine the regular weekly working time. Is a daily working time greater than the value in I2, then the break-duration (H2) is automatically diverted. The computed working times per day are spent for control purposes. They are stored in F1:F7. F8 contains the weekly working time. 1 hr. = 1,0 50 Min. = 0,833 45 Min. = 0,75 30 Min. = 0,5 20 Min. = 0,333 15 Min. = 0,25 10 Min. = 0,167 5 Min. = 0,083 etc. (min/60 = hours) These information must be adjusted only once. 2. Actual month, year and holidays Type in actual month to C10, actual year to D10. I recommend every month to copy the file and or the worksheet and to work only with the copy ! In line 11 to 41 the actual month will be prepared. Holidays in this month are automatically calculated if you have prepared this once. In the field "feiert" (C48:Q49) the holidays must be defined uniquely. All holidays which can fall at a working day must be defined. These holidays are then considered automatically. In line 48 the dates of the holidays has to be defined. Use the function =DATUM($B$9; month; day) (DATE in english ?!) If the date of the holiday are relative to eastern, then you can use the cell B49 as reference (Easter Sunday). E.g. to define ascension day write down: =DATUM($B$49+39) (DATE...) this means 39 days after Easter Sunday ! More days dependent to Easter Sunday: -52 Weiberfastnacht =DATUM($B$49-52) this are days -48 Rosenmontag =DATUM($B$49-48) related on -47 Fastnacht ... the german -46 Aschermittwoch carnival ;-) - 2 Good Friday + 0 Easter Sunday + 1 Easter Monday +39 Ascension Day +49 Pentecost +50 Pentecost Monday +60 Corpus Christi Please consider, that in this spreadsheet you can use only up to max. 15 holidays ! In line 49 you can write down a short memo for the holiday. This memo is used in place of the daily working time on that days... Please consider to define the holidays in the right sequence in the year ! The holidays in the sheets are the holidays for the german state Nordrhein-Westfalen with regional changes. Special holiday-definitions like the bank-holidays: this days are defined to be on a weekday (Monday...Sunday) at/after a fixed date. These types of holidays can defined in a formula too. Look at the example in the (hidden) cell R48: (WENN=IF, RUNDEN=ROUND, DATUM=DATE...) =WENN( 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) < 0; DATUM($B$9;12;31)+ 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) +7; DATUM($B$9;12;31)+ 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) ) This example calculates the date of the first sunday at/after the 31.12. Copy this formula in a cell for the holidays and change it: 1. In DATUM($B$9;12;31) replace the 12 through the desired month replace the 31 through the desired day (5 times) 2. the 1 before -REST...) through the daynumber of the needed weekday. (3 times). daynumber: 0=Sa, 1=Su, 2=Mo, 3=Tu, 4=We, 5=Th, 6=Fr In PSION-Tab is REST to write as MOD... Any ones holidays can calculated with my PSION OPL freeware-Program KALENDER for S3c and Siena. Look at my homepage -> Psion... definitions of holidays for GB and US: Independance Day (US) 4.7. (fixed date) Labor Day (US) Mo 1.9. (first monday at/after the 1.9.) Martin Luther King Day (US) Mo 15.1. (...) Memorial Day (US) Mo 25.5. Presidents' Day (US) Mo 17.2. Thanksgiving Day (US) Do 23.11. Thanksgiving Friday (US) Fr 24.11. Veterans' Day (US) 11.11. Weihnachtstag (US) 25.12. Boxing Day (GB) 26.12. Christmas Day (GB) 25.12. Easter Monday Holiday (GB) os+1 (means Easter sunday+1) Easter Tuesday Holiday (GB) os+2 May Day Holiday Monday (GB) Mo 1.5. New Year's Day (GB) 1.1. Spring Bank Holiday (GB) Mo 25.5. Summer Bank Holiday (GB) Mo 25.8. August Bank Holiday (GB-I) Mo 1.8. June Bank Holiday (GB-I) Mo 1.6. October Bank Holiday (GB-I) Mo 25.10. St.Patrick's Day (GB-I) 17.3. St.Stephen's Day (GB-I) 26.12. 12th of July (GB-N) 12.7. January Bank Holiday (GB-S) 2.1. Spring Bank Holiday (GB-S) Mo 1.5. Summer Bank Holiday (GB-S) Mo 1.8. 3. Projects If needed, you can divide your daily working time in more than one projects. The denotations has to fill in H9:P9. The first (mean) Project becomes the time which is calculated: daily working time minus the other 9 projects. If the number in H is negative, than you have more time distributed to the 9 projects than you haved worked at this day ! The monthly sum for every project is shown. Additional a graphik can be called (Diamond Button at the Psion). The graphic shows the projects with there parts... The input here has to be in hours, e.g. an hour and 30 minutes has to be written as 1,5 ! Use: Adapt the sheet to your special situation. At the start of a new month copy the sheet. Use the copy for your work. After the month you can archive this copy when needed... Write the number of the new month to cell C10 and if needed (december->january) the year (2 digits, year 2000 = 100) to D10. The new month is now calculated... In the lines 11-41 you can see the following data: row A: weekday (Mo, Tu, ...Su) row B: date (day.month.) row C: begin of working time. A suggestion is automatically placed here (if you have defined your weekly scheduling). If needed you can overwrite the planned time. Type in a ' and the time in HH:MM, e.g. '07:50 or '08:55... In this case row D is actualized... row D: end of daily working time (same as row C for begin...). Overwrite if necessary... The break is considered automatically. The Psion calculates in row E the actual daily workingtime (without break). If a holiday has been identified, here stands the memo of the day. If you have worked, you have to put the time manually in! In row F and G the difference to a "normal" day is written out (if you have changed the rows C or D!). row F: time in hours row G: time in hours:minutes In the rows I to Q you can enter the time for the special projects (only in hours, e.g. 1,5 for 1 hour 30 minutes). If the sum of the individual projects is smaller than the day working time, the rest is autom. written to the mean project in row H, also the following formula is used: H = E - SUMME(I:Q) H = mean project E = day working time I:Q = more projects... If you don't have more than one project, the mean projekt (row H) also includes the complete day working time (row E). In row R you can enter a remark like e.g. "sick", "vacation" and enter this time under a project like "Absent"... SUMMARY In line 43 the monthly sum is calculated: row E: actual working time row F: difference to the planned working time rows H-Q: sum of each project cell E44: Actual month hours cell F45: here you can enter the credit/debit time of the last month in hours ! cell F46: here is calculated the credit/debit time for the next month: Planned working time minus actual working time plus credit/debit last month. The cells F45 and F46 are (calculated) in hours. In H45 and H46 they were translated to hours:minutes so it is easier to read. Questions, mistakes, whishes (money ;-)) and so on via Email to me: http://galupki.de Please take a look at my homepage at http://www.germany.net/teilnehmer/100.1894/Juergen.htm Psion-related freeware at http://www.germany.net/teilnehmer/100.1894/psion.htm