Adding times together

[namliam]

well without looking at the sheet, the difference of 4 and 100 is a formatting problem.
24 hours in a day, 24, 48, 72, 96, 4 days 4 hours....
The cell in 20 Jan is still formated as a "normal" time, instead of the [h]:mm format you have in 6 Jan

The Total Special Leave is likely to be the same problem, looking at your spreadsheet... it isnt. The problem is you have merged cells, merged cells are "located" on the left most column, i.e. O:O instead of the P you have right now in the SUM
Edit: if you are unsure where the values are in column O or Column P, a simple solution is to just sum both O and P
=Sum(O519) instead of =Sum(P519)

Really cant see much reason for all the extra work of the merged cells, why not keep more into individual cells rather than merged cells and save yourself not only a lot of work but also a lot of headaches like this O vs P problem

 

[david.paton]

Thanks guys, working good now

 

[david.paton]

My boss thinks it is really good but now wants something else.

She says that people can accrue a maximum of 30 hours of time in lieu. I was thinking that you would need to have a IF statement in the F29 cell that said that if the time in lieu calculation ie F27-F28 was greater than 30:00, or 30 hours, than the Time in lieu carried forward would be limited to 30 hours or 30:00. I wasn't sure how to do this with the time format as I tried to enter a If statement in but it didn't seem compatible with the time 30:00 in the formula.

 

[namliam]

Simplest solution is to take some cell and enter 30:00:00 as a value (with the [h]:mm format on it) and hide the contents by making the text white then make this formula
=IF('6 Jan'!F29> J39, J39, '6 Jan'!F29)

In my case I put the 30 hours into J39

Offcourse you may need to take into account -30 as well as 30

 

[lovemeall143]

hello.Ive got the same quetion..but im doing it in access whenever I put this formula on the expression
TOTAL:[SUN]+[MON]+[TUES]+[WED]+[THURS]+[FRI]+[SAT]

I got nothing, what should be wrong in my formula?

 

[david.paton]

Problem with that solution is that in F29, I want to have the difference between the total hours worked, sick hours etc and the contracted hours but if I enter F27-F28 into the Value_if_true part of the IF function, I don't get the answer.

Using the formula =IF('6 Jan'!F29> J39, J39, '6 Jan'!F29) doesn't take into account finding the difference between F27 and F28.

 

[namliam]

Love, you probably have empty values in your summation, you cannot work with empty values (NULL) at all.... try wrapping each of your fields in the NZ function

 

[namliam]

David, my formula works for 20th of jan, for "Time in lieu brought forward"

If you want to do it with the calculation in 6 Jan F29, your formula would look something like:
=IF(F27-F28>J39,J39,F27-F28)

 

[RainLover]

hello.Ive got the same quetion..but im doing it in access whenever I put this formula on the expression
TOTAL:[SUN]+[MON]+[TUES]+[WED]+[THURS]+[FRI]+[SAT]

I got nothing, what should be wrong in my formula?

You really should be asking this in a separate thread in Access so as not to confuse. Perhaps a moderator Like Paul will move this. But it would be better for you to do it and then send me a PM so I can follow where you went to.

Anyway back to your problem.

What Format is each one of these.":[SUN]+[MON]+[TUES]+[WED]+[THURS]+[FRI]+[SAT]"

What is some sample data. Please look at the Table, for this info if you can.

What are you expecting the result to be. Are you adding Times.

 

[david.paton]

David, my formula works for 20th of jan, for "Time in lieu brought forward"

If you want to do it with the calculation in 6 Jan F29, your formula would look something like:
=IF(F27-F28>J39,J39,F27-F28)

But I don't get it. If you have the above formula entered in on cell 20 Jan!F21 for the time in lieu carried forward, it uses the difference between F27 and F28 from the previous sheet.

I thought the formula in F21 might read something like this

=IF('6 Jan'!F27-'6 Jan'!F28>L3,L3,'6 Jan'!F27-'6 Jan'!F28)

What is wrong with this formula?

I just added 30:00 in cell L3.

 

[namliam]

No my previous formula:=IF('6 Jan'!F29> J39, J39, '6 Jan'!F29)
was intended for 20 Jan F21

=IF(F27-F28>J39,J39,F27-F28)
is intended to be used on 6 Jan F29

I thought the formula in F21 might read something like this

=IF('6 Jan'!F27-'6 Jan'!F28>L3,L3,'6 Jan'!F27-'6 Jan'!F28)

What is wrong with this formula?

You would be correct that formula works, the only issue I can see with this formula is that it is intented to work with +30 hours, where you may be working with -30 hours?
If you want to work with both, you get something like:
=IF('6 Jan'!F29>L3,L3,IF('6 Jan'!F29<M3,M3,'6 Jan'!F29))

Formula for M3 is =-1*L3
Obviously if you only want to work with -30 and dont care about the +30 you only need the red part.

I would also suggest a "time in lieu LOST" kind of information on the sheet(s) so that atleast it is visible someplace and not just "gets rounded down"

 

[namliam]

By the by, if you are still copying the sheets for every 2 week period, I hope you have some code to do that for you and are not manually copying the sheet 26 times for a year (or more for multiple years)

 

[david.paton]

I am copying it 26 times as it doesn't take that long and I am not a code wiz.

How would you recommend I made the time in lieu lost part. Obviously, I would need to add something into cell F21 that to work out any time in lieu above 30 and then put that figure into a cell but I am not sure how to do it.

 

[namliam]

So is it +30 or - 30 hours limit that you are talking about?

If you want to show the lost hours in for example E21, a simple formula like:
=IF('6 Jan'!F27-'6 Jan'!F28>L3,'6 Jan'!F27-'6 Jan'!F28-L3,0)
Would show the lost hours

I would also suggest a formula in B3, along the lines of:
="Payroll for Week Ending " & TEXT(C19,"DD/MMM/YYYY")
Instead of using the dolumn widths to make things fit...

You know you dont need to "hard type" monday, tuesday, instead you can use a format on the date to have it display that right??? Not important anymore but for future reference.

Also your "Total anual leave" is only the total for these 2 weeks, perhaps you would want a "Total anual leave" that is the actual total of the entire year (to date)?

 

[namliam]

I am copying it 26 times as it doesn't take that long and I am not a code wiz.

To copy the sheets and link the information, propegate the date and rename the sheet....
something as simple as:

Sub CopySheets()
    Dim CurrName As String
    Dim i As Integer
    For i = 2 To 26
        CurrName = ActiveSheet.Name ' Remember the previous sheets name
        Sheets(ActiveSheet.Name).Copy After:=Sheets(Sheets.Count) ' Copy the current sheet to a new one
        Range("C5").FormulaR1C1 = #1/7/2018# + 14 * i ' write the new date into C5
        ActiveSheet.Name = Format(Range("C5"), "DD MMM") ' Rename the sheet
        ' Adjust the formula
        Range("F21").FormulaR1C1 = "=IF('" & CurrName & "'!R[6]C-'" & CurrName & "'!R[7]C>R[-18]C[6],R[-18]C[6],'" & CurrName & "'!R[6]C-'" & CurrName & "'!R[7]C)"
        Range("A1").Select
    Next i
End Sub

 

[RainLover]

I am copying it 26 times as it doesn't take that long and I am not a code wiz.

How would you recommend I made the time in lieu lost part. Obviously, I would need to add something into cell F21 that to work out any time in lieu above 30 and then put that figure into a cell but I am not sure how to do it.

David

I have been watching this post with much interest. It is my opinion that things are getting too complicated for this Time Management.

Do you have the time and ability to change to Access. If you do I would suggest that you do so.

Here is a suggestion. Have a close look and see if this Spreadsheet Solution will handle the current number of employees and then double it. Also will it be able to handle the Government Requirement of keeping records for 5 Years or whatever your country requires.

If you have to record sick pay annual leave and all of those other things will your system handle that also.