Hours and minutes

[Detectiveclem]

Can any help me?

I am trying to finalise a time sheet.
All the cells I am referring to have been custom formulated as[h]:mm
At Cell S13 Ihave a formula which shows the total number of hours and minutes worked over aone week (7 day period).
In Cell P2 I havethe fixed value of 36:00. Thisrepresents the total hours expected to be worked over the week.
And only If cell S13 has any value greater than 0 then Iwant to show in cell T13 only thosehours and minutes either as a minus if less than 36:00 or a plus if greaterthan 36:00.

For example if Cell S13 is blank or contains 0:00 then Iwant T14 to remain blank.
E.G. If Cell S13 has a total of 23:30 I want Cell T13 toshow as (minus) -12:30
E.G. If Cell S13 has a total of 45:15 then I want cell T13to show as 9:15
And finally if C13 has a total of 36:00 then T13 should show0:00 or blank.
Any help would be greatly appreciated.

 

[etaf]

Re: Hours and minutes question

=IF( OR( S13 = P2 , S13 = 0 , S13 = "" ) , "" , S13 - P2 )
that should show what you need

if P2 is greater than S13 - then it will be negative and if S13 is greater than P2 it will be positive

If S13 is 36 hours or identical to P3 , OR if S13 is zero or If S13 is blank - then it will show blank

BUT you talk of T14 and T13 as a result - which cell did you want to use OR both ?
if BOTH what are the rules for T13 and what are the rules for T14

 

[Tetra201]

Re: Hours and minutes question

See if the following formula works for you:

=IF(N(S13)>0,IF(S13<$P$2,"-","")&TEXT(ABS(S13-$P$2),"[h]:mm"),"")

 

[joeu2004]

Re: Hours and minutes question

[... deleted by me ... Tetra beat me to it]

 

[Detectiveclem]

Re: Hours and minutes question

Hi etaf, your formula works perfectly. Thank you very much.

 

[Detectiveclem]

Re: Hours and minutes question

Hi Tetra201,

Thank you this seems to work as does etaf's.

 

[Detectiveclem]

Re: Hours and minutes question

Hi Tetra201, I have repeated your formula for other weeks and everything is fine, however I also have a running total cell which simply adds all the weeks totals together (- and + numbers) but this either is blank or shows #VALUE !. Can you assist with me with this? Also is it possible to have the negative numbers showing in red? Thank you again.

 

[Detectiveclem]

Re: Hours and minutes question

Hi again, this formula only seems to work if the number is positive, not negative, call you help?

 

[etaf]

Re: Hours and minutes question

=IF( OR( S13 = P2, S13 = 0, S13 = "" ), "", IF(S13 - P2<0,("-"&TEXT(ABS(S13-P13),"[H]:MM")),S13-P2) )

BUT the negative is a text NOT a number
so could do in a conditional format formula
left(cell, 1) = "-"
to format red

 

[Detectiveclem]

Re: Hours and minutes question

Thank you etaf, this now works perfectly. I have copied this formula to capture other weeks therefore each week shows if I have worked less than or more than 36hrs. I now want cell T2 t show the total (plus or minus) hours I have in my banked hours, but when I simply add or Sum each week I get the #VALUE ! sign. Can you please assist? Thank you