Error in the Calculations-Formulas in Throughput and Cycletime Calculator
Closed this issue · 5 comments
There is an issue the the Calculations of indirect completed dates and indirect start dates in the table Calculations if you use dates with timestamps in "Your Data".
you compare dates (e.g. "2015.01.28 00:00") in the Work in Progress Table with the indirect completed and start dates (to calculate "Count Started" and "Count Completed") that can be in the format a "2015.01.28 12:34:00".
If you round the "indirect completed dates" (Calculations!A:A) and "indirect start dates" (Calculations!B:B) and subtract half a day, you should have the issue solved and the CFD by Day should work for any kind of dates pasted in the "Your Data" table
-> even though the CFD should die ;-)
:) little "d" on the die part.
Hate to be a bother, any chance you could email me a spreadsheet with the dates in your format in it? Does it only happen in the CFD or have you seen it elsewhere? I can only see it in the CFD.
Regards,
Troy
I just sent it.. sorry for the delay
I think i can just trim the fraction part and its in date only (no time). Tested on my side and it "worked on my machine"
was:
=IF(INDEX(StartDateValues,ROW(A2))>0,INDEX(StartDateValues,ROW(A2)),"")
becomes:
=IF(INDEX(StartDateValues,ROW(A2))>0,INT(INDEX(StartDateValues,ROW(A2))),"")
Checked in a fix. Can you test on your data (the file never made it to me, where did you send it?)
I sent the email to troy.magennis@focusedobjective.com - its content:
Hi Troy!
Please find enough sample-data in the attachment.
(as requested in #15)The data comes straight out of an excelmakro I wrote to queries all jira-tickets of a kanban-board by
start-date and not the creation-date. (-> the date the ticket left the first unbounded queue)the international versions of Excel behave strangly with date formats.
On my english excel (mac) it doesn't work to copy/paste the data from the attachment,
but if I open the excel-file (saved with a german version of excel) it works fine.I also attached a Version of your file with the proposed alterations.
You can (hopefully) also see, that the item cycle time chart goes bananas with its dates-axis.
that doesn't happen, when I exclude unresolved tickets.
github doesn't allow me to attach text-files to comments, so I put it in my dropbox:
https://dl.dropboxusercontent.com/u/6659308/resolved_started.txt
can you check your spam folder? maybe my email ended up there.
cheers, markus
OK. Tested with sample data, although only in US locale. Times are now ignored, anything on the same day has the same time. Thanks you markus for the test data. I'd love you to confirm your data loads correctly with the very latest copy (just updated).
I also found issues when testing where blank values pasted into the data section of this spreadsheet gave errors in some calculations. Its weird, but these rows with no data return false to ISBLANK() and true to > 0! So, i specifically had to (TRIM(cell) != "" AND cell > 0). I think this should capture more cases of how data might be corrupted on a clipboard copy and paste.
I'm going to close this. Re-open if you think the calculations are still in error.
Regards,
Troy