Excel Date Range, adding zero value in missing date

[satish12334]

Hi,
I have an excel with Date column (with missing date) and value column. I have to fill the missing dates in the Date column with zero. Also, if same date in repeated with more than once, it should be consolidate with one date instance and the relevant values should be summed up with single value. And the series to be continued till the end of the month.

Attached the excel with Date column and Value column.

[Pete_UK]

Your dates in column A are not real dates - they are text values which look like dates, and as such you can't do any arithmetic directly on them. Can the process which creates the two columns be easily modified to return the dates correctly?

Hope this helps.

Pete

[josephteh]

Try in F3, copy down: =SUMIFS(B$3:B$100,A$3:A$100,E3)

[josephteh]

@Pete, SUMIFS or SUMIF can pick up the text-dates correctly as long as they are in the correct format.

[Pete_UK]

You can use the following formula in cell D3 (note that I have inserted a few columns so that you can compare my solution with what you had prepared manually):

=MIN(--(A3:A31))

This is an array formula, so if you are using Excel 2019 as you profile states then you need to confirm the formula using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter. If you are using a later version of Excel then the CSE is not required.

Format the cell as a date in the style you prefer, and then use this formula in D4:

=D3+1

Copy this down to cell D30 (which will be the 28th of the month if you start with the 1st of the month). You can then use this formula in D31:

=IF(D30="","",IF(MONTH(D30+1)=MONTH(D30),D30+1,""))

and copy this down to D33, which will then cope with months that are longer than 28 days.

You can then use this formula in E3:

=IF(D3="","",SUMPRODUCT(--($A$3:$A$33=TEXT(D3,"dd/mm/yyyy")),($B$3:$B$33*1)))

and copy this down to E33. If you want to show the individual contributions to your totals, as in your sample file, then you can use this array* formula in F3:

=IF(D3="","",TEXTJOIN("+",TRUE,IF($A$3:$A$33=TEXT(D3,"dd/mm/yyyy"),$B$3:$B$33,"")))

* You need to commit this formula using CSE, as previously advised, and then you can copy down to F33.

The attached file shows this in operation.

Hope this helps.

Pete

[Pete_UK]

@Pete, SUMIFS or SUMIF can pick up the text-dates correctly as long as they are in the correct format.

Yes, I know, but I wanted this to be dynamic (by using the MIN function to find the starting date), and so it needed to be proper dates. All the missing dates have to be generated as well - you have assumed them to exist in column E. Thanks for making the point, though.

Pete