Forum Discussion
SUMIFS function not working with date range
I am attempting to calculate debits from a ledger within monthly date ranges. However, I am only getting a "0" as a result. As far as I can tell, I am doing everything correctly. I have formatted all...
Also, my main ledger is in another worksheet in this same workbook. I attempted to change the master ledger and add the column that you suggested, but I only received a "VALUE!" error in the cells. When I copied the ledger data over to the worksheet that has my formula, it seems to work. I don't think I understand why that would make a difference
as you sort of realized, the problem was not with your formula but the dates in the table being text and not numerical/dates. Even though you set the formatting of that column to be "DATE" that doesn't convert text to date values, it just says to excel to show a VALUE in a date format. For example if you have "abc" in a cell and set it to format "DATE" it doesn't magically become some date. Same goes for "11/8/2021" because that is text (in quotes). As shown above the function NUMBERVALUE or better yet DATEVALUE can be used to convert text that looks like a date into a VALUE excel understands. Alternatively you can highlight that column and use Text to Columns (under the Data tab) to convert the text to actual values.
- So using this logic (excuse the pun), I might be able to import the date data, convert it to a numerical date for the formula's purposes, but then have the "displayed" formatting change the appearance of the data back to a date format while still maintaining the usefulness of the data in my formula?
- so when I use the Text to Columns it did change it to a Long Date formatting appearance and yes I just went back up to the formatting options and changed it to show as Short Date at which point it looked the same as how it started but now is actual Values instead of Text.