Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Flexi Time calculator (2000)

    Hi, I have to create a spreadsheet which will calculate the flexi time over a four week period. i am able to add/subtract the times in the days, but i want to carry the balance forward, which may be credit or debit and calculate the accumulated flexi time for that period.

    Any ideas
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Flexi Time calculator (2000)

    I have two comments:
    (1) You can't format negative numbers as time. Since the balance can be negative, you must format it as a number; if you like, it can be hidden. You can display the absolute value in another cell and format that as time, and either use conditional formatting or yet another cell to indicate credit or debit.
    (2) To calculate the balance, you need to supply some info about hours to be worked. I have attached a modified workbook that probably doesn't do exactly what you want, but it may give you some ideas.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    Blue Mountains, New South Wales, Australia
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    <P ID="edit" class=small>(Edited by WebGenii on 23-Oct-02 23:16. )</P>My employer moved away from Excel to a VB program because XL is so sloppy. Copy and pasting a block of cells to the flex sheet sometimes led to the paste overwriting protected cells on the right.

    My approach has been to build a 12 month Workbook. This still requires manual carry overs to the next period. Another colleague has continued to build a workbook each year for recalcitrants who don't want to use the VB program.

    I have attached a 2 period workbook I built for the olympics.
    Grant
    grobinson@dlwc.nsw.gov.au
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    Thank you very much for that, I was thinking along the lines of if statements, but hadn't quite got that far. i've passed it on to the person who requested it and will probably use it myself!

    Michelle

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Flexi Time calculator (2000)

    Hi Michelle,

    Here's a 12-month flex sheet that I created. The format is different to what you're working on, but it might be helpful. It's normally protected (to stop people clobbering things that should be left alone) but I've unprotected it so that you can play with it. Apart from the layout, one major difference between this one and yours is that times are entered in decimal format (ie 7.30 instead of 7:30). The reason for this is to simplify data entry - it also avoids the -hh:mm issue.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    Thanks for that. A lot of work seems to have gone into it. i'll have to spend a bit of time getting my head around it.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    You can have time formatted as negative if you use the 1904 date system (Tools-Options-Caluclations tab).
    I built a timesheet in Excel a while ago which operate over a week and will carry time forward to the next week. it also has the ability to add on extra time generated by working after 7pm (time and half). I have attached a copy of this (it doesn't use the 1904 system but I have one that does). I would welcome all feedback about this.

    PaulJ

    ps The file does use macros which have never caused me a problem but do virus scan it first as I will not be held responsible.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    Thats really useful, thanks. I've got so many ideas now I am not sure where to start! However, how can I accomodate annual leave (7.24 per day) without it giving me hours in debt?

    Michelle

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Flexi Time calculator (2000)

    Michelle,
    Annual leave is the same as working a standard day. On the timesheet I posted, I use the 'Adjustments' box to add the days that I have annual leave. I then add a note on each day that I have annual leave to make it clear where the adjustments come from. There are a few logical functions that convert any negative time back to normal time and then change the flag to hours in debt. This would not be necessary if the timesheet used the 1904 date system.
    Anything else ?

    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •