Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Breaking out the year and month from a date field

0

Is there an issue with having multiple extraction of year and/or month information from one line.

I'm using the following formula which works one one column of dates but not on another.

=YEAR([@[Sale Date]])..................in column 'Sale Year'

=MONTH([@[Sale Date]])...............in column 'Sale Month'

As you can see the formula seems to want to make up something different under the 'Del Year' column.   Incidentally, I put an 'if' formula in front of it but it makes no difference.

=IF([@[Del Date]]="","",YEAR([@[Del Date]]))

I'm a tad confused so thanks for anyone who can shed some light.   I will be hoping to do the same with columns C/O Year and C/O Month.   It's a sales/delivery/carryover workbook that i'm attempting to master.

cheers, David

I note that my screen shot of the actual worksheet doesn't appear, so should be attached (i hope)

[Files removed] (Don)

excelformulas-functions
posted by: unonovo (rep: 6) on Thu Jun 8, 2017 at 2:29 am
Answer
Discuss

Discussion

For whatever reason, I cannot open your .xlsm file. Can you convert to macro-free and reattach?
gebobs (rep: 30) Jun 8, '17 at 9:20 am
Add to Discussion

Answers

0
Selected Answer

I attempted too.  I even changed the file ext to xls, where I was able to pull up on screen, however, there were only 2 cells with info.  Both had "david toulson" Cell A2 + A3.

Looking at your description though, I believe you are using TABLE formatting.  Perhaps remove the TABLE (convert to named Range) to remove the square brackets around the field names...see if that helps???   Otherwise, like Don said, save as different format, as I was unable to open either.

posted by: queue (rep: 467) on Thu Jun 8, 2017 at 10:28 am
Discuss

Discussion

Thanks peoples, i will try the remove table option, failing that i will attach a macro free work book sample.   Again appreciate the help.
unonovo (rep: 6) Jun 8, '17 at 8:17 pm
Ok, i created the workbook as a .xlsx workbook and tried the 'year' and 'month' functions again without success.   Hopefully i have attached the workbook here for comment
unonovo (rep: 6) Jun 9, '17 at 1:13 am
Cannot find where to attach the sample, clues?
unonovo (rep: 6) Jun 9, '17 at 1:15 am
found where to attach the .xlsx sample.   i also should add i changed it from a table to a range.
cheers
unonovo (rep: 6) Jun 9, '17 at 1:36 am
 ....A swing....and a miss!  Still unable to open up file, new one too!.  What version Excel are you using?  Perhaps saving as Excel 97 format?  Otherwise perhaps a CSV (note: only the current tab would be saved, NOT multiple tabs).   
queue (rep: 467) Jun 9, '17 at 9:46 am
Add to Discussion
0

Hey guys, thanks for your help along the way, i managed to sort out my problem with a light bulb moment.......wasn't a formula problem really, it was formatting issue.  Inexperience, dumb when i think about it, but working through it logically over time, gave a result.   Again MANY MANY thanks for you help along the way, it really is appreciated.

David

posted by: unonovo (rep: 6) on Thu Jun 15, 2017 at 6:54 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login