Hello,
I am creating a template that starts with a time series of daily values for the past year (the specific dates will depend on what day I am updating it for).
I need to be able to manipulate monthly data from the time series. I am running into issues because there are gaps in the data. I am only looking at data for work days which means that running a vlookup on a month end date will not produce any value if the last day of the month was not a workday. Additionally, the data isn't always produced everyday so there are natural gaps.
Is there a way that I can have excel search through the time series and find the first value and the last value for each month to produce a monthly summary? I will need the solution to be leverageable for 22 different, identical sheets in my workbook and please keep in mind the specific months/dates will change each time I update it.
Thanks very much in advance!
I am creating a template that starts with a time series of daily values for the past year (the specific dates will depend on what day I am updating it for).
I need to be able to manipulate monthly data from the time series. I am running into issues because there are gaps in the data. I am only looking at data for work days which means that running a vlookup on a month end date will not produce any value if the last day of the month was not a workday. Additionally, the data isn't always produced everyday so there are natural gaps.
Is there a way that I can have excel search through the time series and find the first value and the last value for each month to produce a monthly summary? I will need the solution to be leverageable for 22 different, identical sheets in my workbook and please keep in mind the specific months/dates will change each time I update it.
Thanks very much in advance!
