0.000092593 PAs at risk
Posted By Paul Cooper | last update 02-Aug 2011
Ive got to do some Job planning sums . We keep a departmental on call diary ( web-based , that stores the data in MySQL database). I need to produce an export of this in Excel for the management. I can produce a text output of the data fields and then import them into Excel ( well, Openoffice - but the principle and problems are the same). As seven pm is 'the witching hour when time changes from standard (1PA = 4 hours) to premium (1PA=3 hours) , I need to identify time entries that are at or span 7pm on a weekday.
Excel has a function 'TIMEVALUE that converts a time into a number - its fractions of a day from midnight.So here are the formuale , including the comparison function
and the results - the comparison is FALSE - ie it thinks that the 2 time values are not the same
WTF? Some of the sums werent working because 7pm wasn't being accurately identified.
If I increase the decimal places displayed , the number are like this
ie the twelth decimal place. I think its to do with spreadsheets and floating point arithmetic.
the solutions i could try out
- dont do a B1=B3 comparison, do a ABS(B1-B3) < a very small number
- instead of a comparison X>= Y do NOT(X
Or change the time I compare the database entries from 19:00 to 18:59:59.
This will result in the calculation being wrong by 0.000092593 PAs where either the start of finish time for the on call activity is 19:00. do you think the Trust will mind ?
Posted by andrea tate on 02-Aug 2011 13:08
paul - you don't have a fur lined anorak for nothing - love you for trying to sort this for us all!!
[i'm sure there will be sensible contributions soon]
Posted by Grant Forrest on 02-Aug 2011 14:08
I wouldn't count on it ;-)
Posted by Douglas duncan on 02-Apr 2012 20:04
Job planning seems to be done on weak data generally. Some of us in Edinburgh are using this series of we pages to help. Using mobile phones we are collecting workload data for individuals and departments (on call).
A demo version (a bit out of date now, things have moved on! ) is here http://www.cinderellagasman.com/DEMO/WORKDIARY/one.php
Posted by Alan Hope on 13-Dec 2017 17:12
Excel includes three functions (ROUND, ROUNDUP, and ROUNDDOWN) that round values to a SPECIFIED number of digits.