how to read value from a concatinated cell

[stuart_robets]

Hi

Once I concatenate two dates into one in a cell, how do I then use that cell in a range in a function such as SUMIF, every time I concatenate, the value of the SUMIF function becomes zero, I'm assume its becuase it sees the formulea and not the value, i've tried to format the cell, but it doesnt work, is there another way?

 

[snjpverma]

could you post a sample data, reflecting how your data actually looks like?

 

[stuart_robets]

day | date | value | description

5 | 05/02/2018 | £300 | HSBC


Todays date 04/02/2018
next pay day


bills to next pay day



above is a sample from the data base, what i am trying to achieve is auto complete the bills to next pay day, based on two dates, 1. todays date and 2. next pay day

i am trying to use a SUMIFS function =SUMIFS(Table1[[Value ]],Table1[Date],">"&I2,Table1[Date],"<"&I3)

against the table

however i am trying to auto complete the date box based on todays date, so the day value will be the same, as that is the day the bill is paid, but the month and year changes, based on the current month.

so i was using this

=CONCATENATE(TEXT(A2,"dd")&"/"&TEXT($J$2, "mm/yyyy"))

first of all i copied the "todays date into another field and removed the day, then used the function above to auto fill the dates in the table, however when i do this the value of "bills to next pay day" is zero, but when i manually add the dates in the table, it works. so is there an alternative way of filling the date box, or am i using the concatenate wrong?


thanks