Excel Query
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 =
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 =
0
Comments
-
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.0
-
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.0 -
Beautiful_bobo wrote: »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?0 -
I did try that and it did not work. I will try again. Cheers0
-
It's possible that the cells are validated, have you tried selecting the cells going to tools, validation, clear all?0
-
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.0 -
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)0 -
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
dan0 -
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 helps0 -
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.0
-
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.0
Categories
- All Categories
- 1.2K Books to buy and sell
- 2.3K General discussion
- 12.5K For AAT students
- 318 NEW! Qualifications 2022
- 161 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 87 AAT Level 4 Diploma in Professional Accounting
- 8.8K For accounting professionals
- 23 coronavirus (Covid-19)
- 272 VAT
- 92 Software
- 273 Tax
- 135 Bookkeeping
- 7.2K General accounting discussion
- 200 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership