problem with time subtraction

[lozzauk13]

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 ar
31 per year times by 7.18 (36.5 hour week divided by 5) to give me
total of holiday hours. That bits fine.

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

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

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

cheer

 

[Claus Busch]

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

 

[Hokie_16]

Put the if statement on column D instead of column E because that i
what is really changing, right? Also, time is always a little funky i
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 woul
=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
break of 1 hour otherwise report back a 30 min break. Last is you
total billable hours in column E:

=C-D

Does that anwer your question

 

[lozzauk13]

Put the if statement on column D instead of column E because that i
what is really changing, right? Also, time is always a little funky i
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 woul
=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
break of 1 hour otherwise report back a 30 min break. Last is you
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_1
everytime i try i get the false command when trying =time(h,m,s).
forgot to tell you guys i use a list on another sheet, so i can do m
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 b
working fine, except boxes that are empty are displaying 00:30:0
instead of nothing.

also because of changing the formats to times instead of custom, befor
we used to work out the holiday as - holiday days allocated times 7.1
(1 day in a 36.5 hour week. but now i cant do that as im trying to ge
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
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 shif
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 exampl
11:00:00 + 11:00:00 + 11:00:00 - instead of saying 33:00:00 to minus of
my total holiday hours remaining it will say 09:00:0

 

[lozzauk13]

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_1
everytime i try i get the false command when trying =time(h,m,s).
forgot to tell you guys i use a list on another sheet, so i can do m
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 b
working fine, except boxes that are empty are displaying 00:30:0
instead of nothing

also because of changing the formats to times instead of custom, befor
we used to work out the holiday as - holiday days allocated times 7.1
(1 day in a 36.5 hour week. but now i cant do that as im trying to ge
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
get back 10:18:0

stumped

and thanks guys so far for your hel
edit: hmmm think ive sorta found out whats going on in the total shif
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 exampl
11:00:00 + 11:00:00 + 11:00:00 - instead of saying 33:00:00 to minus of
my total holiday hours remaining it will say 09:00:0

added a pictur

right ive solved some of that issue, the only issues i have at th
moment now is different amounts on the same calculation and also i
showing 30 mins break on blank boxes(i need the blanks incase a dat
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.5

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

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

ive attached a quick workings out to show you what i started with an
what i have no

+-------------------------------------------------------------------
|Filename: holidayproblem.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=857
+-------------------------------------------------------------------

 

[Claus Busch]

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

 

[lozzauk13]

Hi

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


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


Regard
Claus Busc

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Claus Busch]

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