Breaking out the year and month from a date field


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]]) column 'Sale Year'

=MONTH([@[Sale Date]]) 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)



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


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.



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.
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: 333) Jun 9, '17 at 9:46 am
Add to Discussion

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.



Answer the Question

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