D
Dutch Guy at Work
After importing a report from an external application (CMS Supervisor), I
need to do a SUMIF to add all values that match a time frame.
The CMS report, unfortunately, uses a time-only format (H:nn), so the
fields' date is defaulted to 1/0/00. To prevent discrepancies, on a second
worksheet I generated a list of times to match the times in the report (7:00
- 23:30 in half hour intervals), and I preform a SUMIF on the times...
.... and I found that Excel thinks that certain times do not match.
One of the intervals missed is the 9:30 AM one. A quick formatting of the
cells gave me an interesting result... apparently, two identical times are
*not* identical - between the two cells there's a numerical difference of...
[deep breath]
0.000000000000000666133814775094
[exhale]
Cell one, showing 1/0/00 9:30, has a number value of 0.3958333333333340
Cell two (and all the other cells that have the 9:30 listing), *also*
showing 1/0/00 9:30, has a number value of 0.3958333333333330
(that's as accurate as the workbook is allowing me to get it)
Needless to say, when doing a SUMIF on the date fields, it fails because the
values aren't the same.
Is there a workaround for this issue? The Excel workbook is supposed to be
an automated sheet, that is supposed to take the raw report, and with minimal
actions (that's where the SUMIF comes in place) calculate the totals for each
time frame, which are to be inserted into a database...
need to do a SUMIF to add all values that match a time frame.
The CMS report, unfortunately, uses a time-only format (H:nn), so the
fields' date is defaulted to 1/0/00. To prevent discrepancies, on a second
worksheet I generated a list of times to match the times in the report (7:00
- 23:30 in half hour intervals), and I preform a SUMIF on the times...
.... and I found that Excel thinks that certain times do not match.
One of the intervals missed is the 9:30 AM one. A quick formatting of the
cells gave me an interesting result... apparently, two identical times are
*not* identical - between the two cells there's a numerical difference of...
[deep breath]
0.000000000000000666133814775094
[exhale]
Cell one, showing 1/0/00 9:30, has a number value of 0.3958333333333340
Cell two (and all the other cells that have the 9:30 listing), *also*
showing 1/0/00 9:30, has a number value of 0.3958333333333330
(that's as accurate as the workbook is allowing me to get it)
Needless to say, when doing a SUMIF on the date fields, it fails because the
values aren't the same.
Is there a workaround for this issue? The Excel workbook is supposed to be
an automated sheet, that is supposed to take the raw report, and with minimal
actions (that's where the SUMIF comes in place) calculate the totals for each
time frame, which are to be inserted into a database...