Convert Date String to Date and Sum the value

Hi,

Can any one help how to covert the date string to date and sum the value based on below condition.

I have data set with Date as below in excel and i need to consider data for next six month i.e., for current month it should pick values from 07.2018 to 12.2018 and in next month it should pick values from 08.2018 to 01.2019

Input

Name	Date	Value
A	07.2018	100
A	08.2018	200
A	09.2018	300
A	10.2018	400
A	01.2019	500
B	08.2018	600
B	09.2018	700
B	10.2018	800
C	08.2018	900
C	09.2018	1000
C	02.2019	1100

Out Put

Name	Value
A	1000
B	1300
C	1900

Help

Accepted answers

BenMoss

You can convert your date in many ways. I would use a formula in this case to create a date based on your field.

Right([field],4)+'-'+left([field],2)+'-01'

Then create a second formula to calculate the datemonth currently.

Datetimetrim(Datetimenow(),'month')

Finally create a third calculation and use the datetimedifference() function to compare your two date fields.

Then you can use a filter tool to acknowledge only those in the next 6 months.

Finally you can use a summarize tool after your filter to aggregate your data. Grouping by your name field and then summing the values.

Ben

All comments

BenMoss

Shank

Thank You i am able to fix it

BenMoss

No problem @Shank I'm glad we could get you to a solution! The way you structured your question and including sample data made it very easy to understand the problem!

Ben

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335