# Formatting Issue

#### ExcelAtEverything

##### Active Member
In an Excel "YTD report" which is sent to me, I am copying an entire sheet, and pasting into a blank sheet in my own seperate workbook. There are two columns of importance in this report": In column B are dates & times but only appearing as a dates (because row B is formatted as custom > dd/mm/yy). The 2nd column of importance is column AE which contain dollar amounts (of sales from the corresponding dates).

I'm trying to use SUMIF in my own sheet pull all totals from yesterday. The formula below is what I have:
=SUMIF('YTD Report'!B:B,TODAY()-1,'YTD Report'!AE:AE)

The formula however is returning "\$0.00". I am certain that the reason for this has to do with row B on the pasted in sheet. Again, each cell in row B contains not just a date, but a time:
(01/02/21 8:25:43 AM) for example... even though only the date is visible when looking at the cell because its formatted to custom > dd/mmyy.
In fact, if I replace a couple of those cells in row b with a manually typed in "01/02/02", it then starts adding them up correctly. So my question is this: Is it possible to adjust my SUMIF formula to look only to the date in that cell without the need to make adjustments to the YTD report?

#### ExcelAtEverything

##### Active Member
Wait, this dollar amount that the formula is returning seems unbelievably large. I see that there are two arguments in the formula. What are each of them doing exactly? I feel like this is pulling more than just yesterday.
That got it! Bam!Thank you KRICE! (and Steve The Fish!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### steve the fish

##### Well-known Member
Wait, this dollar amount that the formula is returning seems unbelievably large. I see that there are two arguments in the formula. What are each of them doing exactly? I feel like this is pulling more than just yesterday.
Its definitely not summing anything other than yesterday. Thats a data issue. You need to look at your data.

#### ExcelAtEverything

##### Active Member
To take something away you use a conditional sum to say 'if this cell equals this then sum the adjacent cells'. A date and time is not equal to just a date so what you originally wrote could never work. What these new formulas do is say is the date/time greater than today-1 but also less than today. This will encompass all date/times within any particular date.
That logic is a little confusing as it seems to be saying opposite things. So long story short, is that guaranteed to simply pull all entries for yesterday's date ony?

#### ExcelAtEverything

##### Active Member
Its definitely not summing anything other than yesterday. Thats a data issue. You need to look at your data.
10/4. As long as I know that, then I can figure that part out. Thanks.

#### KRice

##### Well-known Member

Yes, a date alone is a whole number (decimal component is zero). When a date/time format is used, anything other than midnight (00:00 hours) will have a non-zero decimal component. Steve's approach accepts all date/time combinations between yesterday at midnight (including midnight) up through yesterday at 23:59:59.99999 hours, just before the calendar rolls over into today...so all of yesterday's date/time combinations are summed. My SUMPRODUCT solution strips the decimal component off the date/time numeric values, essentially turning all of them into dates as they would exist at midnight. And that formula also sums values only for entries with a date stamp of yesterday.

#### steve the fish

##### Well-known Member
Perhaps you need to understand that to excel the date is just a whole number and the time is a fraction of 1. You can see this for yourself if you format the date column to General. Midday yesterday for example is 44264.5 on a windows machine (a mac would produce a different number but it would still be a number). 44264 is the date produced by the number of days since 1/1/1900 inclusive. The 0.5 is the time hence midday being 0.5.

#### ExcelAtEverything

##### Active Member
Steve the fish, that is the most interesting thing I have learned so far today. That is both fascinating, and great info to know. Thanks!

#### ExcelAtEverything

##### Active Member
KRice, absolutely perfect explanation. Thanks for that!

Replies
0
Views
100
Replies
13
Views
164
Replies
5
Views
119
Replies
1
Views
197
Replies
5
Views
399

1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

### 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.

### Which adblocker are you using?

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

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