+ Reply to Thread
Results 1 to 5 of 5

Time troubles

  1. #1
    Registered User
    Join Date
    09-11-2008
    Location
    Devon. UK
    Posts
    5

    Time troubles

    Hi all,

    I just got a job as a manager in a shop, part of my job is to organize a rota. I do this, but the old rota is crude and time consuming... so i made a new one.

    What i really wanted is not only to have a start/stop time but have the hours clocked up in that shift, then to take 30 mins off for break.

    Then to add the total hours so i can lazily check i haven't given out overtime inadvertently.

    I have done all of this but with errors. I got the times and hours worked sorted, then the breaks taken off (after i found that 0.020833 is 30 mins).

    The problem i have is when i add them all up i dont get the right total. I think it is because I have left 2 shifts blank (resulting in #####) for the rest days coz they differ week from week.

    So is there a way of getting rid of this #### without having to change the code week by week.

    I have attached a shorter version for you to look at, please dont do the work for me but tell me how to do it myself (or how can i learn?)

    Thanks in advance

    Jaffa
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Hello Jaffa,

    Rather than use a number like 0.020833 you can just use "0:30" in the formula, i.e.

    =O5-N5-"0:30"

    ##### represents negative time (because you are still subtracting 30 minutes from a zero length shift when O5 and N5 are both zero).

    If there is no shift worked I'd be inclined to leave O5 and N5 blank (rather than put zeroes in) and then use this extension of the above

    =IF(COUNT(N5,O5)=2,O5-N5-"0:30","")

    ...or you could stick with the original formula but ensure that it can't return a values less than zero, i.e.

    =MAX(0,O5-N5-"0:30")

    If you have a blank returned by formula in P5 and others then to make B13 formula work correctly change to

    =SUM(D5,G5,J5,M5,P5,S5)

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    a tip
    if you multiply time by 24
    it'll give it in decimal so time 00:30(30 mins) will change to 0.5 10:40 will change to 10.67 useful for calculating hourly pay(format cell as number))

  4. #4
    Registered User
    Join Date
    09-11-2008
    Location
    Devon. UK
    Posts
    5
    thanks allot that should just about do it for me!

  5. #5
    Registered User
    Join Date
    09-11-2008
    Location
    Devon. UK
    Posts
    5
    just one other thing, how can you display negative numbers, ie I have a girl contracted 28 hours, I add all her hours up and have a box indicating the total.
    Then to make life easier I subtract the contracted hours, if they are below 28 i will know how much by so as not to over pay them.

    At the moment #### is all i get

    thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing time
    By Ziggy_ks1 in forum Excel General
    Replies: 1
    Last Post: 04-21-2008, 10:42 PM
  2. Time w/in Each Hour of Day btwn a Time Range
    By gbrogmus in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-30-2007, 03:01 AM
  3. Date and Time Graphs
    By sach0025 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-10-2007, 01:07 AM
  4. Time troubles
    By spinkung in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-13-2006, 10:24 AM
  5. Assignment overDUE PLEASE PLEASE HELP!!
    By undergrad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-29-2006, 12:34 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1