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

JamesBowdidge

New Member
Joined
Nov 8, 2018
Messages
46
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Nov 8, 2018
Messages
46
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top