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


19:00:00 =TIMEVALUE(B1)

2011-04-07 19:00:00 =TIMEVALUE(B3)

comparison =(B3=B1)


and the results - the comparison is FALSE - ie it thinks that  the 2 time values are not the same

19:00 0.79166667

2011-04-07 19:00 0.79166667

comparison FALSE


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

19:00:00 0.79166666666666700000

2011-04-07 19:00:00 0.79166666666424100000

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 ?

4 comment(s)

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



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.

Might help.

show all blogs