Combine Data to Common Annual Date

**jgatesmaus** · 08-07-2013, 10:49 AM

I have payroll data broken out between various different time periods of varying length. I am trying to build a model that will automatically display the amount in a certain annual time period (e.g. 8/1/2011-8/1/2012), for multiple years.

So far, in the first row of calculations I put the beginning date for the data, and in the second I have the end date. In the third, I find the number of days difference between the two. In the fourth, I find out how much payroll is paid out each day by dividing total payroll for that data set by the number of days in the data set. From this point on, I am hitting a snag.

Is there a way that I can build the model so that it will do the following:

A) Determine which annual time period(s) the data will fall into

e.g 8/1/2010-2011, 8/1/2011-2012. If the data set went from 5/1/2011-9/30/2012, it would straddle the two annual periods and need to be split.

B) Determine how many days fall in each annual period, and multiply that number by the daily payroll.

C) Sum all data sets by the appropriate annual period. I have about 15 different payroll periods over a span of 4 years, and I need the end result to be payroll between 8/1 of each year and the next.

Any help would be greatly appreciated

**6StringJazzer** · 08-07-2013, 03:29 PM

Welcome to the Forum!

It will be much easier to understand your problem if you provide your file. This allows us to see your data, layout, code (if there is any), much easier than describing it in several paragraphs. You still have to explain what you want, but it makes the whole process easier. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like. And once we understand the problem, having your file will let us experiment with your data, formulas, and code, and possibly attach a file with a completed solution.

To post a file:

Under the text box where you type your reply click the Go Advanced button.
In the next screen look above the reply box and click the paper click icon. You will get a pop-up screen.
In the pop-up, click Add then Select to select a file from your local drive
Upload it
Click Done to attach it.

It will be displayed underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

**jgatesmaus** · 08-08-2013, 06:11 PM

Effective Date Workbook.xlsx

My workbook is attached. The area above the black row is the data dump, and the area below is where I started my workbook. The yellow highlighted area is my goal.

**FDibbins** · 08-08-2013, 06:35 PM

Hi and welcome to the foum

This is problematic on at least 2 different levels.

1st, your "dates" in row 1 are not dates, they are text
2nd, your "dates" in row 1 have considerable - and varying - overlaps...
4/1/2009-10/1/2009
4/1/2009-12/31/2009

I would suggest that, if you can, you starndardize on the dates in row 1, and use actual dates, not a text date range

**jgatesmaus** · 08-09-2013, 02:58 PM

I would ignore the data dump above the black line, as I really just used it to get to the information below the black line. The majority of what I am trying to do is to get rid of the overlaps, because there are so many different payroll periods.

**FDibbins** · 08-09-2013, 03:53 PM

Im not too interested in the actual data row 2:16, my comment was related to the the format of the "dates" in the 1st row.

We could probably fix this by adding to helper rows, 1 for the start date and 1 for the end date...
start date =DATEVALUE(MID(B1,SEARCH("-",B1,1)+1,99))
end date =DATEVALUE(LEFT(B1,SEARCH("-",B1,1)-1))

Then use a sumifs() for the calcs?

**jgatesmaus** · 08-12-2013, 10:22 AM

Is that different than what I have in row 20 and 21?

**FDibbins** · 08-12-2013, 10:43 AM

Hmm OK I didnt see that. I will take a look again

Combine Data to Common Annual Date

LinkBack

Thread Tools

Rate This Thread

Display

Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Re: Combine Data to Common Annual Date

Thread Information

Users Browsing this Thread

Similar Threads

How to combine data from multiple rows when common column headings exist?

Combine data onto one row based on common field value

Combine Rows of Data on Common Value

[SOLVED] Combine 2 spreadsheets w/1 common column of data, text and number

combine cells under a common heading

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions