Flexi Working Timesheet - formula result not correct

Hi

I've inherited a flexi accrual timesheet in work from a previous manager (who no longer works at my place) and I must implement it in my project for my agents.

I've noticed a possible error in the calculation of one formula that has only occurred when the accrued flexi hours calculated at 15hr 59m, as a result of the deduction of -1hr 59mins from 17h 48m - see row18 highlighted on the attachment, and then cols W to AC for the formulae that calculate the running totals.

The formulae in hidden cols Z and AA both return 15hr 59m (or 15.59) however the formula in hidden col AB returns 15.99 which is replicated into col AC, the visible running totals column an agent will see. Next to these I have used prev INT based formulae to show this as 16.39 however these two cols don't normally exist in the sheet (i put them into test the formulae). However 16.39 is incorrect as the result in AC18 should be 15.59.

Is there something I'm missing or is this a loophole the formula in AB just can't account for, even though it appears to be designed for specifically that purpose?

See next paragraphs though which could potentially be the cause and therefore the solution.

When I initially received the worksheet, the content of col O to Q were just values (see headers in worksheet). The worksheet was initially only designed to be Mon-Fri (working week). Col O originally showed as values, 7.25 on Mon Tue Thur Fri and in col P it would be 445 and then 225 (3h 45m) in col Q. However for some reason Wed was 7.20, with 440 and 220 respectively (never found out why, possibly a hangover from civil service as are public ngo). Over the Mon-Fri this results in an accrual of 30mins flexi acccrual (5, 5, 10, 5, 5) which is allowed in our flexi policy as a sort of perk.

However I need the timesheet to incorporate weekends (as I and others managers in the project may work from home at wkends) so I converted it to calendar weeks.

I researched some formulae for col O and P to insert values based on the weekdays matched to dates in col A, or 0 if weekends as any hours worked here would be accrual. To make this easier I spread the above 30mins out across Mon-Fri resulting in a standard day of 7.24 for all, with 444 in col P, but maintaining the 225 in col Q as this is the standard half day (ie a 6min daily accrual).

I have a feeling that what I did in col O to Q has had an adverse effect on the formula in col AB.

If that's the case, rather than trying to fix the AB formula, I will need to fix col O to Q to once again show 7.25, 7.25, 7.20, 7.25, 7.25 etc. However I'd like this to stay as a formula which will update automatically based on the date, otherwise if it's just values only, they will have to be updated each time as the days will change between rows based on the month. I can't rely on the agents changing this manually each time, and an autofill would be more practical and less prone to error.

I appreciate if the above is confusing, I've tried to give as much info as I could to explain the rationale behind what I have done and what I think I need to do to fix it.

I have attached a copy of my timesheet plus a copy of the original one I was sent so you can see what I mean.

Many thanks in advance.
Liam

Hi Shivya

Thanks for your reply. I appreciate confusion may result if there is superfluous data, however there isn't. The data in the worksheet LLFWT is exactly as it needs to be to show the error that is happening, and is explained in my post with appropriate highlights on the worksheet itself. The second attachment is the original worksheet to show a comparison to what I need to achieve, as I explained in my post as 'my desired result'. If I had attached the full LLFWT as it is currently set up, then there would be superflous, confusing data. I edited it specifically for this reason.

My trust in this forum and its users is not misplaced, as I resolved the issue using a mixture of threads from other users. I have now marked this thread as solved.

Thanks
Liam