Edit: Attaching workbook
Hi,
I would like to be able to return the month value in the following way, being the current month Value into Month 1:
Sep-17 = 1
Aug-17 = 2
Jul-17 = 3
This means that once we are in October, I want the values to reclassify the following way:
Oct-17 = 1
Sep-17 = 2
Aug-17 = 3
Jul-17 = 4
Thanks in advance,
D2S
Last edited by D2S; 09-24-2017 at 05:27 PM. Reason: Attaching Workbook
.
Not enough information. You understand what you are seeing but we dont.
Need a sample workbook or images or something more to work with.
Thanks for taking a look Logit, attaching a workbook, maybe that will be more clear.
Regards,
D2S
Try this in C3:
=DATEDIF(1&B3,TODAY(),"m")
In C3:
=DATEDIF(1&B3,"1Aug-17","m")+1
Chance Aug to Sep, Oct,... for next months
Quang PT
Another way. Put 1st of month date next to headers. eg in C1 8/1/2017. Then array enter this formula in C3 and fill down.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Fill down until you get blanks. Then copy the range and paste into F3 and I3.Formula:
Please Login or Register to view this content.
Dave
.
I've been messing around with some code trying to create something that is as automatic as possible so the OP won't have to cut / paste / edit / paste / etc.
This is as far as I have been able to get however, when the cell numbers are written to the cells, instead of sequentially listing by Step -1, they are presenting as Step -2.
I've attempted a number of edits but cannot get the numbering as required.
Do you have a solution ?
Please Login or Register to view this content.
@ Logit,
What you describe sounds like what I encountered with formula. I found that when the start date was the month previous to the ones in row 1 it all came together. But that's with formula.
I attempted to find something related to that in your code. Unfortunately I'm not good when it comes to reading code. So I don't have any solid suggestions.
Does this help anyway?
.
Thank you for responding. Regretfully, no ... I'm still stumped. It's taken me several days to get this far. I'll keep plugging away in hopes of a solution.
Sorry about that.
To get automation for OP's layout VBA is definitely the way to go IMPO so wish you the best.
.
Ok ... got a working project .. although it seems like a chinsee way of getting to the goal. Must be a better, more concise way to accomplish this.
Basically, the macro (or part of it) is directed at everyother row and each part of the macro concentrates on the rows the other part of the macro does not. (Confusing ? Ya .. I know).
It works .... as additional months are added, the macro follows suit with the updated numbering.
BUT, CAN SOMEONE REDUCE THE LINES OF CODE TO SOMETHING MORE ELEGANT ? My mind is fried.
Please Login or Register to view this content.
@ Logit
this would be my approach (writes next to yours)
Please Login or Register to view this content.
.
Hey NoSparks ! Good to 'see' you again. Been awhile.
I'm receiving a TYPE MISMATCH error on this line :Please Login or Register to view this content.
Tried to clear it up but no go. ???
I do to if the months are real dates, but in your file from post #6, and the OP's file from post #1,
they aren't.... they're text.
Always work with real dates so that you can do date arithmetic.
Then one way for example, with dates in K3:K18 starting with 07/01/2016, working down to 10/01/2016
and with the current date in L1 and in L3
Formula:Please Login or Register to view this content.
and in L4 copied down
Formula:Please Login or Register to view this content.
See attached.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
.
I highlighted all of the dates in Col B, FORMAT, DATE, Mar-01
Just to be certain, I re-entered the dates in each row using: 8/2016, 9/2016, etc.
Still receiving error. What has this old, feeble mind missed ?
????
Richard: Thank you for the formula. The OP was looking for an auto-method of having the Column numbering update when a new month is added
in Col B. Hoping a VBA approach would provide such.
Doooh!. Logit I converted in the upload and forgot to mention that part in my original post. My bad. Apologies to D2S and you.Originally Posted by Richard Buttrey
You're not missing anything.
The problem is exactly what Richard says... if you're going to work with dates... then work with dates.
Please Login or Register to view this content.
.
Me thinks I have misunderstood a basic premise of Excel formatting. *embarrassed*
If I format the column as indicated previously, that is not formatting as a "date" ?
If that is the case, how does one format a cell as a date ?
NoSparks .. thanks for the last macro. That is so much cleaner and works as the OP desired. He now has his answer !
Hi,.
Me thinks I have misunderstood a basic premise of Excel formatting. *embarrassed*
If I format the column as indicated previously, that is not formatting as a "date" ?
If that is the case, how does one format a cell as a date ?
NoSparks .. thanks for the last macro. That is so much cleaner and works as the OP desired. He now has his answer !
Not certain I understand the question but, and perhaps stating the obvious, you can only format a cell to show a date IF the cell contains a date number. i.e. anything between zero and seemingly 2,958,465 (31 December 9999).
If the cell contains text like your original file then no amount of attempting to format it as a date will change anything.
Once it is a date number you can of course format it to show various presentations of a date. e.g. dd/mm/yyyy, mmmm yyyy, ...etc.
.
Is the attachment (Col B) in Post #16 a DATE or TEXT ?
If TEXT, how should it be edited ?
It is a number (date). To check find an empty column and use =ISNUMBER(B3) and fill down. It will return TRUE if it is a number.
To change text to dates select column B then click DATA >> Text to columns >> Fixed width >> click next. At step 3. Be sure to select Date from the formatting options. Click Finish.
.
The ISNUMBER formula confirmed they were DATES. So I hopefully can presume using the FORMAT / DATE is correct.
Thank you.
Yes. You're welcome._____________________________
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks