I get an export from our accounting system that lists the days in one column, with the month across the top. What I would like to be able to do is normalize the dates to be in one column.
I have attached a sample from the report I receive. With a column added to show the desired effect.
My initial thought is a very long nested IF statement, but I'm SURE there has to be a better way and any help or guidance is appreciated.
To note, in this instance, the year will always be constant @ 2022
Are you still using Excel 2016?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
No, sorry that changed very recently, I am using Office 365
See if this works for you:
=DATE(YEAR(TODAY()),MONTH(DATEVALUE(INDEX($G$1:$O$1,MATCH(999999999999,$G2:$O2,1))&" 1")),$F2)
or:
=DATE(2022,MONTH(DATEVALUE(INDEX($G$1:$O$1,MATCH(999999999999,$G2:$O2,1))&" 1")),$F2)
is that what you want?
Account Number Account Description Payee Transaction Description Cost Center Date Value Some Account Unassigned Sample 1 Some Cost 1 Some Account Unassigned Sample 2 Some Cost 2 Some Account Unassigned Sample 3 Some Cost 3 Some Account Unassigned Sample 4 Some Cost 4 Some Account Unassigned Sample 5 Some Cost 5 Some Account Unassigned Sample 6 Some Cost 6 Some Account Unassigned Sample 7 Some Cost 7
Yes, both of these work, although I am not quite sure how
Thank you very much for your help!
Power Query
Please Login or Register to view this content.
Or try in P2 and copy down:Formula:
Please Login or Register to view this content.
It is. Now that I have the date normalized, I am going to use Power Query to create a proper data set like the image you have.
I am open to another way if you have one?
Thank you for you input
This also works!
use @username because we don't know who your posts are to
Last edited by sandy666; 11-14-2022 at 10:18 AM.
@sandy666 - The code that @Czeslaw gave achieves what you are demonstrating in your picture. Thank you for your input! And sorry, I thought when I reply to a comment, it was tagged to the person I was replying to.
@Czeslaw - This is excellent, this is the approach that I will take! I know I did not mention Power Query in my original ask, but this is the easiest way to achieve what I need
@HansDouwe - This also works. I can't believe this ask can be achieved by such a concise formula
@AliGW - I marked this thread Solved based on your answer. Thank you!
Glad to have helped.
If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Could be so.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks