Excel Query

Beautiful_bobo
Beautiful_bobo Well-KnownRegistered Posts: 106
Morning
Please can someone help, I am trying to create an all singing all dancing spreadsheet to work out peoples hours. I am having trouble with the night shift formula. I know I have done it before but I now cannot remember. Please put me out of my misery!!!

Many Thanks


Col A Col B
8:00:00 13:00:00 = Col B - Col A = 5hrs
19:30:00 0:00:00 =
0:00:00 11:30:00 =

Comments

  • jilt
    jilt Font Of All Knowledge Registered Posts: 2,903
    I think for the 19.30 t 0.00 shift you'll have to 24.00 instead of 0.00. 0.00 to 11.30 will obviously be fine. That's the simpliest way I can think of right now.
  • Woooof
    Woooof Well-Known Registered Posts: 174
    jilt wrote: »
    I think for the 19.30 t 0.00 shift you'll have to 24.00 instead of 0.00. 0.00 to 11.30 will obviously be fine. That's the simpliest way I can think of right now.
    That's the easiest way in my opinion :)
  • Beautiful_bobo
    Beautiful_bobo Well-Known Registered Posts: 106
    Excel Query

    Many thanks for your quick replies, I tried putting 24:00 in but it does not like that and I get " The value you entered is not valid, a user has restricted values that can be entered into this cell"

    I shall keep trying, in between doing purchase ledger, sales ledger, answering the phone, payroll and people wanting to rearrange furiture for no reason at all.
  • A-Vic
    A-Vic Expertise Guaranteed Registered Posts: 6,970
    Many thanks for your quick replies, I tried putting 24:00 in but it does not like that and I get " The value you entered is not valid, a user has restricted values that can be entered into this cell"

    I shall keep trying, in between doing purchase ledger, sales ledger, answering the phone, payroll and people wanting to rearrange furiture for no reason at all.

    have you tried changing the format of the cells?
  • Beautiful_bobo
    Beautiful_bobo Well-Known Registered Posts: 106
    I did try that and it did not work. I will try again. Cheers
  • Toffeemadblue
    Toffeemadblue Well-Known Registered Posts: 102
    It's possible that the cells are validated, have you tried selecting the cells going to tools, validation, clear all?
  • dantray
    dantray Feels At Home Registered Posts: 72
    if you create a custom format whereby you lock the hours:

    [hh]:mm:ss

    it will allow you to change 00:00 to 24:00 and then the formula entered in column C =(b1-a1) will calculate difference.
  • CJC
    CJC Font Of All Knowledge Registered Posts: 1,657
    Assuming the time in Col B isn't greater than 24 hours after Col A adding 24 works

    =IF(B1<A1,B1+24-A1,B1-A1)
  • dantray
    dantray Feels At Home Registered Posts: 72
    Can I just add that if you are processing large amounts of payroll what i just posted may not always be the most practical method.

    this issue has also been covered in this article regarding payroll in excel - (about 1/5 way down).

    http://www.meadinkent.co.uk/excel-shifts.htm

    thanks
    dan
  • Marga
    Marga Experienced Mentor Registered Posts: 981
    hello i use excel 2007 and i tried to enter the formulae and it worked


    08:00:00 13:00:00 05:00:00 usual A-B
    19:30:00 00:00:00 04:30:00 here i entered 24:00:00 which excel automatically changed to 00:00:00
    0:00:00 11:30:00 11:30:00 here i entered 00:00:00 which excel automatically changed it to 0:00:00

    the format of my cells are "custom"

    hope that helps
  • Beautiful_bobo
    Beautiful_bobo Well-Known Registered Posts: 106
    Many Thanks to all for your help. I think I have sussed it now it was to do with the validation of the cell, I pressed a lot of buttons and hit the keyboard and it might have worked now, fingers crossed.
  • CJC
    CJC Font Of All Knowledge Registered Posts: 1,657
    Sorry, but changing the data to make the formula work is getting things the wrong way around. If you need to enter midnight as 00:00 in one column and as 24:00 in another you're asking for trouble, especially if someone else ever comes to use the spreadsheet. Moreover, if you should need to cover times after midnight you will also run in to trouble - having to represent 1AM as 25:00 is just wrong. As for validation, as 00:00 is the generally accepted way of representing midnight then validating to prevent entries outside of the rage 00:00-23:59 seems quite sensible.

    Either use the formula I suggest or that in the article dantray linked to below (they amount to the same thing) and keep your data consistent.
Privacy Policy