Formula for calculating inmate layout time and fines.

[Lord451]

I'm trying to write a time calculator for inmate times. So far I've got a very thorough system setup, but there's one flaw.

If an inmate is working, he gets double credit (two days credit, and double credit for fines, usually 100/day). Right now there's a hole in the math so that if an inmate begins working after he's completed his time but before his fines should be complete, but stops before his fines are complete it throws off the dates (example, he's been in jail for a month, became a working inmate after 20 days, and the judge gives him a 10 day sentence and a $2,000 fine).

If anybody needs me to clarify any pieces of it let me know, it's become kind of a baroque system at this point in order to properly give him credit if he started before the time was done, then stopped, then laid out the remainder of the fines.

Jail management is a pain, and any help would be appreciated, and anything I can clarify I'll be happy to.

Time calculator 1.16 EXPERIMENTAL.xlsx

[dilipandey]

Hi Lord451,

I can see the formulas already in your spreadsheet.. please highlight where you are facing issues... if formula is inaccurate, remove them and enter manually but highlight. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Lord451]

OK. The two highlighted spots, A-12 and A-17. The part of the formula I need is the a-12 IF statement when D12=0.

With the numbers included now, it's set to start the correct end date. If you set the 1st credit date (A-17) earlier, it correctly moves the end date backwards. The problem is when the date moves forward (he begins getting double credit after he should be released) then the formula moves the end date forward.

If you need any other clarifications, let me know and I'll try to explain what I've done.

Here's the slightly more highlighted version.
Time calculator 1.16 EXPERIMENTAL.xlsx