time issues - adding changing decimals when time expressed as number...

[JamesBowdidge]

Hi,

ill try and make some sense here...

ok, If I write "13:00" in a cell and format as a number I get "0.541666666666667"

If I write 07:00 in A1, "07:15" in B1 then use the handle and drag to the right.. the 13:00 in this series formatted as a number is now "0.541666666666666" (Note the last "6" is now a "7"...)

when trying to look up the first (without the 7) time against the new (with 7) time it obviously doesnt find it..

anyone know whats happening here and if theres a way to resolve?

Ive been chasing my tail all day and finally discovered this to be the issue with my spreadsheet! Is there some sort of pattern to this error, a way to rectify easily?

thanks

J

 

[RoryA]

It's a floating point issue. You could avoid it by using a TIME formula instead, such as:

=TIME(7,15*(COLUMNS($A1:A1)-1),0)

copied across from the first cell.

 

[JamesBowdidge]

Thanks for the reply.. tested your formula and it appears to do as stated! thanks.. although Im not sure what its actually doing? as if I format the results as numbers they are the same for both the normal dragged time and the time that the formula calculates..

Ill summarise the actual issue I have...

I receive a file periodically that currently has 2700 odd rows (although it does grow), 4 columns across, first one is the mane of the site, second is day of week (like this "Monday" etc), third is the time in this format "14:00:00", 4th is the number of appointments the slot contains. Each site only has the operational hours included...

So manchester may have a row on a monday for 10:00 but croydon may not have this row... if that makes sense..

I am trying to count the appointments in each slot and present them in a table format for each site.. BUT this time issue is causing an issue as some its finding and other its not..

How do I convert this raw list into the correct format IE sort this floating point issue in the data prior to analysis?

any ideas?

thanks

J

 

[RoryA]

If you increase the decimals, the TIME formula should not have the issue of the last decimals changing that happens when you fill the series across.