• filter:
  • Page :
  • 1
  • Text Only
  • Search this Topic »
rated:
I looked online and haven't found a solution if there is any.  I have a column with dates of the month 9/1/16, 9/4/16, 9/15/16 etc.  Each month I modify the file manually, I go down the column and change to 10/1/6, 10/4/16 etc.  Anyone know of a way to change it faster or automatically just the month? 

I use Excel 2016 by the way.

Excel Column Example 

Thanks for visiting FatWallet.com. Join for free to remove this ad.
rated:
=DATEADD(m, 1, (datefield))

rated:
Oops that's vba/sql, date add doesn't exist in excel.

for a date in a1
=date(year(a1), month(a1) +1,day(a1))

I would note that if you try this for let's say 1/30/2016, it will fail there is no 2/30

rated:
scrouds said:   Oops that's vba/sql, date add doesn't exist in excel.

for a date in a1
=date(year(a1), month(a1) +1,day(a1))

I would note that if you try this for let's say 1/30/2016, it will fail there is no 2/30

  Thanks for info let me give it a shot

edit: How do I implement this to a current column with dates for September and want to switch to October? 

If i add  =date(year(a1), month(a1) +1,day(a1)) to A1 it complains about circular reference to its own cell. So if i leave it in A1 and change it to =date(year(c7), month(c7) +1,day(c7)) which currently has 9/1/2016 the A1 becomes 10/1/12016. 

rated:
Personally, I don't recommend taking a girl on a MS Excel date.

rated:
scrouds said:   Oops that's vba/sql, date add doesn't exist in excel.

for a date in a1
=date(year(a1), month(a1) +1,day(a1))

I would note that if you try this for let's say 1/30/2016, it will fail there is no 2/30

  Adjust code to increment date +364 (adjust for leap years)

rated:
DenverDiver said:   scrouds said:   Oops that's vba/sql, date add doesn't exist in excel.

for a date in a1
=date(year(a1), month(a1) +1,day(a1))

I would note that if you try this for let's say 1/30/2016, it will fail there is no 2/30

  Adjust code to increment date +364 (adjust for leap years)

I'm not sure how that aligns with the op's question. Truth is he hasn't told us how he handles dates like that. Could happen in other months going from 31 days to 31 too. If he tells us how he handles it, like promotes to next month, I can make a fancier formula.

I've moved from dealing with excel and vba into database stuff but I always enjoy delving back in if someone needs help.

rated:
enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

rated:
sportsfan64 said:   enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.

rated:
kamalktk said:   sportsfan64 said:   enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.


But what if I want to use my PowerPoint?

rated:
burgerwars said:   kamalktk said:   sportsfan64 said:   enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.


But what if I want to use my PowerPoint?

I'm guessing the Outlook isnt so good.

rated:
kamalktk said:   
burgerwars said:   
kamalktk said:   
sportsfan64 said:   
enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.


But what if I want to use my PowerPoint?

I'm guessing the Outlook isnt so good.

  
... Especially if you Publish'er photo online.

rated:
You might be able to Lync up with her.

rated:
Are you Siri-ous? Oh wait, wrong platform.

rated:

After (19.87kB)
Disclaimer

Before (15.60kB)
Disclaimer
Jnetty99 said:   I looked online and haven't found a solution if there is any.  I have a column with dates of the month 9/1/16, 9/4/16, 9/15/16 etc.  Each month I modify the file manually, I go down the column and change to 10/1/6, 10/4/16 etc.  Anyone know of a way to change it faster or automatically just the month? 

I use Excel 2016 by the way.

Excel Column Example

I am NOT an Excel wiz, but the following seemed to work.   Are the days always the same every month (e.g., the 1st, 4th, etc)? 

​If so, this is a bit more tedious initially, but, you do it once and you are done:
1. Take the column you want to mimic dates
2.  In adjoining column type next month's dates
3.  Select both columns
4.  Grab lower "fill" button on the last right most cell and drag to the right (as far as you would like)
5.  Once the adjacent columns are "filled" with the correct data, you can just save that file for future.  Next month, just copy and paste the correct column in.

You should now have duplicate months and days (see below)
This is a fairly basic approach - let me know if it solves your problem
Hope this helps!
 

rated:
enterkey said:   
kamalktk said:   
burgerwars said:   
kamalktk said:   
sportsfan64 said:   
enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.


But what if I want to use my PowerPoint?

I'm guessing the Outlook isnt so good.

  
... Especially if you Publish'er photo online.

She's never going to let you anywhere near her SharePoint.

rated:
kamalktk said:   enterkey said:   
kamalktk said:   
burgerwars said:   
kamalktk said:   
sportsfan64 said:   
enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
Word! 

Yeah, not if you want Access.


But what if I want to use my PowerPoint?

I'm guessing the Outlook isnt so good.

  
... Especially if you Publish'er photo online.

She's never going to let you anywhere near her SharePoint.


Don't get me going about SharePoint. The most convoluted software product made since the invention of software.

I once took a three unit Excel course at a community college (online) just to keep up to date with this stuff. I got an A. I forgot most of it. "If you don't use it, you lose it."

rated:
This should take less than a minute if done correctly.

Assuming you have your dates in column A
Assuming you store the date you last updated the file in cell B1

  • In Cell C1, write "=$A1 + TODAY() - $B$1"
  • This adds the difference (ie. the number of days) between today's date and the last time you updated to the date in cell A1.
  • Copy this formula by dragging the green square down to all the respective cells in column C.
  • Copy the values of column C into column A (use Paste Values (V), instead of regular paste).
  • Update B1 to the current date (don't use the function TODAY(), actually put the date in).


Leap years are no problem. Excel handles it.

  • 1/28/16 + 32 = 2/29/16
  • 1/28/15 + 32 = 3/1/15
  • 2/28/16 + 33 = 4/1/2016
  • 2/28/15 + 33 = 4/2/2016


Also, since the TODAY() function updates automatically, keep column C around for the next time. Each time you open the file, it will show the new dates for column A (assuming cell B1 is accurate). All you'll need to do each month is special paste (Paste Values (V)) and update B1.

rated:
wiseguy1100 said:   
Jnetty99 said:   I looked online and haven't found a solution if there is any.  I have a column with dates of the month 9/1/16, 9/4/16, 9/15/16 etc.  Each month I modify the file manually, I go down the column and change to 10/1/6, 10/4/16 etc.  Anyone know of a way to change it faster or automatically just the month? 

I use Excel 2016 by the way.

Excel Column Example

I am NOT an Excel wiz, but the following seemed to work.   Are the days always the same every month (e.g., the 1st, 4th, etc)? 

​If so, this is a bit more tedious initially, but, you do it once and you are done:
1. Take the column you want to mimic dates
2.  In adjoining column type next month's dates
3.  Select both columns
4.  Grab lower "fill" button on the last right most cell and drag to the right (as far as you would like)
5.  Once the adjacent columns are "filled" with the correct data, you can just save that file for future.  Next month, just copy and paste the correct column in.

You should now have duplicate months and days (see below)
This is a fairly basic approach - let me know if it solves your problem
Hope this helps!

  

The date can change slightly. The excel chart is basically my bills for the month. Column C7-C27 are my due dates. Most are always the same and some change a little bit. 

rated:
If you have a bull that uses a straight thirty day period, just do =(date) + 30

rated:
enterkey said:   Personally, I don't recommend taking a girl on a MS Excel date.
 

  



hmmm, isn't a "microsoft" excel(lent) date sort of an oxymoron?

rated:
Hopefully the excel date ends between some. Sheets.

rated:
scrouds said:   If you have a bull that uses a straight thirty day period, just do =(date) + 30
That's some heavy flow

  • Quick Reply:  Have something quick to contribute? Just reply below and you're done! hide Quick Reply
     
    Click here for full-featured reply.


Disclaimer: By providing links to other sites, FatWallet.com does not guarantee, approve or endorse the information or products available at these sites, nor does a link indicate any association with or endorsement by the linked site to FatWallet.com.

Thanks for visiting FatWallet.com. Join for free to remove this ad.

While FatWallet makes every effort to post correct information, offers are subject to change without notice.
Some exclusions may apply based upon merchant policies.
© 1999-2016