Excel Query

Beautiful_bobo
Beautiful_bobo Registered Posts: 106 Dedicated contributor ๐Ÿฆ‰
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 Registered Posts: 2,903 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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 Registered Posts: 174 Dedicated contributor ๐Ÿฆ‰
    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 Registered Posts: 106 Dedicated contributor ๐Ÿฆ‰
    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 Registered Posts: 6,970 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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 Registered Posts: 106 Dedicated contributor ๐Ÿฆ‰
    I did try that and it did not work. I will try again. Cheers
  • Toffeemadblue
    Toffeemadblue Registered Posts: 102 Dedicated contributor ๐Ÿฆ‰
    It's possible that the cells are validated, have you tried selecting the cells going to tools, validation, clear all?
  • dantray
    dantray Registered Posts: 72 Regular contributor โญ
    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 Registered Posts: 1,657 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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 Registered Posts: 72 Regular contributor โญ
    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 Registered Posts: 981 Epic contributor ๐Ÿ˜
    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 Registered Posts: 106 Dedicated contributor ๐Ÿฆ‰
    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 Registered Posts: 1,657 Beyond epic contributor ๐Ÿง™โ€โ™‚๏ธ
    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