Hi guys i was wondering if anyone can advise the best way to do this.

im working out holiday hours owed to me. so ive done the days which are 31 per year times by 7.18 (36.5 hour week divided by 5) to give me a total of holiday hours. That bits fine.

the issue im having is, im recording the dates booked and the shift pattern.

for example, A1 - 08:00 ... B1 - 18:00 ive used the forumula c1 =sum b1-a1 = 10 hours. then ive done d1 0:30 (break) e1 =sum c1 - d1 = 9:30 which is correct.

however if doing a 12 hour shift then the break is 1 hour, and i cant use the auto forumla as it only does 30 min break on 12 hours. if tried to see if i can do a if command such as if e111:30 - 0:30 = 11:00 the reason ive done it still as - 0:30 is due to auto forumla already minusing 30 but i get an error. i aint that advanced on excel so im really stumped. hoping someone can advise.

cheers

Hi,

Am Fri, 26 Apr 2013 17:01:01 +0100 schrieb lozzauk13:

for example, A1 - 08:00 ... B1 - 18:00 ive used the forumula c1 =sum
b1-a1 = 10 hours. then ive done d1 0:30 (break) e1 =sum c1 - d1 = 9:30
which is correct.

Start in A1, End in B1
If you don't work over midnight:
=B1-A1-IF(B1-A1=0.5,1/24,1/48)
else:
=MOD(B1-A1,1)-IF(MOD(B1-A1,1)=0.5,1/24,1/48)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Put the if statement on column D instead of column E because that is what is really changing, right? Also, time is always a little funky in excel if you don't use it right. So put the following in each column

A:
=time(hour,minute,second) for your example this would would =time(8,0,0)

Then in B do the same thing:
=time(18,0,0)

Column C will be the same =B-C

Column D has your if statement that will look like this:
=IF(C1=TIME(12,0,0),TIME(1,0,0),TIME(0,30,0))

That is saying that if the shift is 12 hours or longer report back a break of 1 hour otherwise report back a 30 min break. Last is your total billable hours in column E:

=C-D

Does that anwer your question?

claus i cant start in a1 and b1 due to other information being in there, i will try your code a little later to edit the cells. and hokie_16 everytime i try i get the false command when trying =time(h,m,s). i forgot to tell you guys i use a list on another sheet, so i can do my drop down box to pick the times.

the code =IF(C1=TIME(12,0,0),TIME(1,0,0),TIME(0,30,0)) seems to be working fine, except boxes that are empty are displaying 00:30:00 instead of nothing.

also because of changing the formats to times instead of custom, before we used to work out the holiday as - holiday days allocated times 7.18 (1 day in a 36.5 hour week. but now i cant do that as im trying to get the sheet to do fully in time, for example old way was 31 days *7.18 = 222.58 hours worth of holiday. ive tried doing 7.18 (time) * 31 but i get back 10:18:00

stumped!

and thanks guys so far for your help
edit: hmmm think ive sorta found out whats going on in the total shift hours box but not sure how to fix it. basically because im using time, it will never go past 23:59:00. so if i did time addition for example 11:00:00 + 11:00:00 + 11:00:00 - instead of saying 33:00:00 to minus off my total holiday hours remaining it will say 09:00:00

added a picture

right ive solved some of that issue, the only issues i have at the moment now is different amounts on the same calculation and also it showing 30 mins break on blank boxes(i need the blanks incase a date gets added)

36.5 hours = 1 week off based on 5 day week. if i divide 36.5 by 5 it = 7.18. so 31 days off x 7.18 = 222.58

if i change all the formats to time, and do it as

36:30 / 5 = 7:18 then if i do 31 x 7:18 that = 226.18 so thats different to the 222.58 and the difference is 003.60 hours.

ive attached a quick workings out to show you what i started with and what i have now

Hi,

Am Sat, 27 Apr 2013 13:34:12 +0100 schrieb lozzauk13:

right ive solved some of that issue, the only issues i have at the
moment now is different amounts on the same calculation and also it
showing 30 mins break on blank boxes(i need the blanks incase a date
gets added)

then try:
=IF(OR(B1="",A1=""),"",B1-A1-IF(B1-A1=0.5,1/24,1/48))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

thanks claus that took away the ###### and ive hidden the break box just so it states the total hours minus the break, its just the difference in 31 x 7.18 and 31 x 7:18 now thats causing the issue.

Hi,

Am Sat, 27 Apr 2013 16:15:16 +0100 schrieb lozzauk13:

thanks claus that took away the ###### and ive hidden the break box just
so it states the total hours minus the break, its just the difference in
31 x 7.18 and 31 x 7:18 now thats causing the issue.

you can't set 7.18 = 7:18
7:18 is 7 hours and 18 minutes
7,18 is 7 hours and 10 minutes

That will make a difference of 4,13 hours or
4:08


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2