increment cells based on date

**jameswhite4684** · 02-20-2015, 04:23 PM

Screenshot from 2015-02-21 04:16:29.png

I am trying to increment cells Edition, Segment, Effective Date.

When Effective date changes to the next month, Segment should increment by one. If Segment reaches greater than 12, Edition will increment to next letter and Segment will return to 1.

In the picture, I am only trying to describe what I am achieving, So really, I would only have the Header in A1, B1, C1, then Edition, Segment, Date always in same row when it increments.

If I could have the Effective date to update on the 20th of each month to the 1st of next month automatically, that would be even better, as it is less prone to user error.

VBA is okay to use also.

I hope somone could help me out as I am stumped.

**Beamernsw** · 02-21-2015, 11:49 AM

Hi James, try this:
In Cell A11:-

Please Login or Register  to view this content.

And in Cell B11:-

Please Login or Register  to view this content.

As for the date changing to the next month on the 20th, I think you'll need VBA.
Unless that date is based on =TODAY()

In that case maybe:-

Please Login or Register  to view this content.

**jameswhite4684** · 02-21-2015, 07:33 PM

Beamernsw, Thank you for your help!

I have a question, I am trying to mess with data part, not formula.

As in my example, If I need it to specify my own Editions and Segments where it needs to start, what part would I exactly change, I guess what I am trying to do is very difficult.

What I would have is a list of many different Editions, on different Segments, but most will have the effective date the first of the month always. I would acutally have a label in Column A. But I can always tweak cell assignments later.

Edition Segment Effective
A 4 03/01/15
C 10 03/01/15
NB 4 03/01/15
N 11 03/01/15
C 11 03/01/15
A 12 03/01/15
B 4 03/01/15

**FDibbins** · 02-21-2015, 07:55 PM

James, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

**jameswhite4684** · 02-21-2015, 08:01 PM

FDibbins, Thank you for the heads up.

Attached is an example spreadsheet.

**FDibbins** · 02-21-2015, 08:14 PM

Im not sure where you want to put this, but for the Segment increase...

	A	B	C	D	E
1	Edition	Segment	Effective
2	G	5	1 Feb 15
3	B	3	1 Feb 15	B	5
4	B	3	1 Feb 15	B	3
5	AZ	11	1 Feb 15	AZ	3
6	N	11	1 Feb 15	N	11

D3=IF(E3>12,CHAR(CODE(A3)+1),A3)
E3=IF(MONTH(C3)=MONTH(C2),B2,B2+1)

This may have a problem with double letters in column A

**jameswhite4684** · 02-21-2015, 08:16 PM

It would be the same exact cell, Im trying to make it, so when the effective date changes, segment will increment up to 12 and roll over the edition on 13 and segment resets to 1

**FDibbins** · 02-21-2015, 08:20 PM

So you will have values in both A and B that you want to have changed based on a date change? Or do you have a seen value in A and B that you want to base the changes on?

**jameswhite4684** · 02-21-2015, 08:33 PM

will have values in both A and B that you want to have changed based on a date change I want to say.

The Value in B will always increase by 1, once per month. The edition / Value A only changes after Value in B exceeds 12 as there are only 12 months in a year.

or

If Value C changes, increase Value B +1, at 13 reset to Value B to 1 and increase Value A +1.

Value C = Date, Value B = Number, Value A= Letter

**FDibbins** · 02-21-2015, 10:09 PM

So those values you have in your file are just dummy data?

**jameswhite4684** · 02-21-2015, 10:13 PM

Yes, but that is acutally how the data is and formatted.

**FDibbins** · 02-21-2015, 10:26 PM

Sorry to draw this out

OK so row 1 will be entered values?
all else will be formulas?

If Value C changes, increase Value B +1, at 13 reset to Value B to 1 and increase Value A +1.

do you want B+1 for any date change, or for every day? meaning, if date changes from Feb 1 to Feb 10 in the next cell (or wont it happen like that?), do you B to increase by 1 or by 0?
and if it changes from Feb 1 to Apr 10, does it change by 1 or by 2?

**jameswhite4684** · 02-21-2015, 11:04 PM

it would change from 1 Feb to 1 March every month, once a month, I would like it to change the 20th of Every month. So on 20 Feb it would change Effective date to the next first of the month which would be 1 March 2015.

I have setup an example: there are four sheets, Sheet one is current month, sheet 2 is what the next month would look like, sheet 3, month after, and sheet 4 just a day after the 20th, as no values should have changed.

But the values would be as if it was only using one sheet.

**FDibbins** · 02-21-2015, 11:49 PM

Assuming your data is all on sheet1...
A3=IF(B2>=12,CHAR(CODE(A2)+1),A2)
B3=IF(B2+1>12,1,IF(MONTH(C3)>MONTH(C2),B2+1,B2))
Both copied down?

**jameswhite4684** · 02-21-2015, 11:57 PM

Dont assume anything being copied down, as each row with have its own unique data. row 2 will not have anything or maynot have similar data to row 3. That is why the chart you pasted has different data on each row, each is unique to each other.

**FDibbins** · 02-22-2015, 12:51 AM

OK, then if you already have data in each cell, you cannot also have a formula in that cell to change its value - cell can have formula OR value, not both

**jameswhite4684** · 02-22-2015, 01:29 AM

Okay, This may be alot simpler.
I have a sheet called "Base" this is the starting point for each item.
I have a sheet called "Monthly" this is where I would like the Segment and Effective date to increase once amonth and only on the 20th of each month.
Segment will increase +1 and Effective date will increase 1 entire month, so it will always be on the 1st of each new month. I do not want a fill down.

I believe formulas will only be on the "Monthly" sheet.

I hope this may be much simplier, as right now I have to many human errors each month when making this work. Editions I am not to worried about as that does not change as often. But if possible I would like also.

Maybe your formulas will work already cause the senrio is the same, except I added the base values which will be static for the most part.

**jameswhite4684** · 02-22-2015, 02:11 AM

Here is what a Final look example would look like, as 20th Feb 15 has past.

I hope this paints a clear picture.

**Beamernsw** · 02-22-2015, 12:08 PM

Hi again James, sorry its taken me a bit to get back.
Not trying to be nasty.....really

But a common problem with these forums is we don't get the full picture of what the posters wan't in the original post. Thats why a lot of our formulas don't work first time.
What you want is a fair bit different (well extra) then what you OP states.
Its all good, but it would have been better for us and you if you got the correct answers 1st time round :D

I think I have what you want.
I have a date that would represent TODAY() in cell F1. You can type dates into that cell to test that it does what you want.
If you are satisfied that it works, you can either replace $F$1 in the D column Formula with TODAY() or you can type =TODAY() into cell F1.
I had problems with the AK title until I change the Base Effective Date to 1-Dec-14 (Hope thats ok)
The vlookup is only looking at 30 rows at the moment (rows 2 to 32) you can change that to suit.
I'm also assuming that Title GA having 6 segments means that 1/7/15 increases Edition and resets segment to 1.

I hope it does what you want James.

Beamer

**Beamernsw** · 02-22-2015, 12:15 PM

Actually, sorry, I have no idea why I used vlookup. I must be more tired than I thought.
If Monthly is going to be a direct list from Base then this will be much easier.

increment cells based on date

LinkBack

Thread Tools

Rate This Thread

Display

increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Re: increment cells based on date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Increment a Number in cells of same column based on how many of a specified data is found

Increment Date Based on Number of Pages Printed

Formula to Increment Value in Cell by One Based on Other Cells

Increment Date based on Worksheet number

[SOLVED] FORMULA TO INCREMENT 25 MONTHS BASED ON A DATE CELL

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions