# Excel Query

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

• 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.
• 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
• 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.
• 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?
• Well-Known Registered Posts: 106
I did try that and it did not work. I will try again. Cheers
• 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?
• 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.
• 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)
• 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
• 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
• 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.
• 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