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

thats not going to add or subtract - values as they are not numbers but text

so we would need to convert to a number again and then can be Summed , but if negative would need to convert back to text
probably need a helper column

The result from the above formula in T13 or T14 - never did clarify this
then
=IF(LEFT(T13,1)="-",((VALUE(RIGHT(T13,LEN(T13)-1)))*-1),VALUE(T13))
converts back to a decimal with the sign
BUT not the Blank cells - so would need to add in there for a zero
then if the result is negative would need to convert again to text

But i'm not sure now of your cell layouts and if you can have a helper column in your sheet

 

[Detectiveclem]

Re: Hours and minutes question

Hi Wayne, Sorry T14 was a typo it should have been T13. I have tried your suggested formal to convert the time to decimal but for example where T13 shows -13:30 your formula turns this into -0.5625. However when use this formula
=INT(T13)*24+HOUR(T13)+ROUND(MINUTE(T13)/60,2) it works but only with positive numbers. Can you assist further? Thanks Paul

 

[Detectiveclem]

Re: Hours and minutes question

Oh, when when it is positive such as 2:00 it shows 0.083333 when I use your formula

 

[etaf]

Re: Hours and minutes question

yes , thats the general format and so change to custom TIME format [H]:MM

 

[Detectiveclem]

Re: Hours and minutes question

Dear Wayne,

Thank you, almost there. The only problem appears to be withthe –minus hours/minutes. The calculation is correct but the cell displays#########################. When I hover over the cell it states that “Dates and Times that are negative are toolarge to display as ######.”

Can you suggest how to remedy this?
Thank you I really appreciate your expertise here.

 

[etaf]

Re: Hours and minutes question

=IF(LEFT(T13,1)="-",((VALUE(RIGHT(T13,LEN(T13)-1)))*-1),VALUE(T13))
converts back to a decimal with the sign
BUT not the Blank cells - so would need to add in there for a zero
then if the result is negative would need to convert again to text


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

would need to add the TEXT part to this formula

=INT(T13)*24+HOUR(T13)+ROUND(MINUTE(T13)/60,2)

But before doing that - are you going to want to do ant type of arithmetic on that value - TEXT would not allow that as before

=IF((INT(T13)*24+HOUR(T13)+ROUND(MINUTE(T13)/60,2)) < 0,TEXT(ABS(INT(T13)*24+HOUR(T13)+ROUND(MINUTE(T13)/60,2)),"H:MM"),(INT(T13)*24+HOUR(T13)+ROUND(MINUTE(T13)/60,2)))

 

[Detectiveclem]

Re: Hours and minutes question

Hi Wayne, I am getting lost now. I am beginning to think it might just be easier to show everything in decimal rather than using the H:MM settings. This is probably simple for you to understand but it's going over my head now. I genuinely appreciate everythinhg you have done for me, we seemed to get so far.

Thanks again, Paul

 

[etaf]

Re: Hours and minutes question

OK
Basically displaying negative values and then using them for calculating is the issue
Negative time is displayed as text and so cannot be used in a calculation , and so if you want to display in H:MM its straightforward
BUT if you then want to use that negative value to forma calculation, then you have to convert to be able to do that

you will still have issues with decimal , as the time will need to be converted

 

[Detectiveclem]

Re: Hours and minutes question

You are right, it's a pity I couldn't send you the time sheet so you could see exactly what I am trying to achieve.

 

[etaf]

Re: Hours and minutes question

you could link to a share , like dropbox, onedrive or icloud or even googledrive with a sample


Rule #4:
Members should not use the Private Message system to request specific assistance. All members are volunteers, contributing their time and expertise where and when they can, and such requests may be deemed harassment (see Rule #1 ).

Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.