Excel Query

Beautiful_bobo

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 =

jilt

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

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

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

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?

Beautiful_bobo

I did try that and it did not work. I will try again. Cheers

Toffeemadblue

It's possible that the cells are validated, have you tried selecting the cells going to tools, validation, clear all?

dantray

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

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

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

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

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

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.