Date Formula Question

[woodsedge]

I have a column of dates like "9-Aug" which, in the cell, translates to "8/9/2018." However, the "9-Aug" should really mean "8/1/09." My question is how can I convert the column of numbers in the "9-Aug" format into a column that reads "8/1/09?" Any help would be most appreciated.

Thank you.

[Special-K]

So the Aug should result in 8
9 should result in the year of 09

Where does the 1 come from?
Will it always be

"month/1/year" ?

You're implying it is since the 1 is not input anywhere.

[Special-K]

There may be a simpler way

=(TEXT(A1,"dd")&"/01/"&TEXT(A1,"mm"))+0

This may not work as I'm using European date format.
(You might have to swap "dd" "mm" around)

[Sam Capricci]

the answer to Special-K's question is important to know. That being said, you can always force it with this...
=DATEVALUE(MONTH(A1)&"/1/0"&DAY(A1)) then format as date.
BUT, the /0 part is also forcing it to be 2009 but if you go to 10 I don't think it will work.

[woodsedge]

The 1 is just the first day of the month and that will remain a constant for the dates in the final product. Does that help?

Thank you!

[Sam Capricci]

well what I gave you returns 8/1/2009 or 8/1/09 if formatted that way. As I noted, I'm not sure what will happen if you go to 2010, I'd have to see an example to see how it might handle that.

[woodsedge]

Thanks Sambo kid! It works up to 2010. After 2010 I get an error message: #VALUE! Then when I view "Evaluate Formula" it shows DATEVALUE("1/1/010").

Thoughts? Please and thank you.

[Sam Capricci]

show me what the date looks like for 2010 so I can see because it isn't readily intuitive to me.

[woodsedge]

Sure. It looks like 10-Jan and inside the cell it's 1/10/2018. Make sense?

[Sam Capricci]

well this change would work... =DATEVALUE(MONTH(A2)&"/1/"&DAY(A2))
BUT, I'm struggling to visualize your data because if I saw a representative sample that shows what you have and what you expect it to be I (or someone else) might be able to write a formula that covers more scenarios.
2nd BUT, this covers both scenarios =IFERROR(DATEVALUE(MONTH(A2)&"/1/0"&DAY(A2)),DATEVALUE(MONTH(A2)&"/1/"&DAY(A2))) but I'm not sure how it will work overall.

[woodsedge]

I'm happy to let you view the file but I cannot seem to attach in in this forum. I can e-mail it to you.

Thank you.

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Sam Capricci]

is the second formula I created above in post #10 not working?
you go to the "go advanced" button below the quick reply window, then mid way down you will see "manage attachments" and click on that. then browse to your sample file and upload it and click ok etc .

[woodsedge]

I believe your second formula is working, Sambo kid, thank you. For whatever reason I've tried two browsers and am still not able to go find the attachment I uploaded. But, I think your second formula is working. I'm trying it now with the remainder of my data set. Thank you.

[FDibbins]

Did you see post #12 on how to upload a file?

[woodsedge]

Yes I saw post #12 and followed the "recipe" but still can't get attachments to work. I'll keep trying. Thank you.

[FDibbins]

What happens when you go through those steps?

[woodsedge]

After I upload the attachment then go to "Go Advanced" to post when I pull-down the attachment icon there are no options from which to choose. Thank you.

[FDibbins]

Dont use the icon, (it sometimes doesnt work), scroll down a bit until you see the MANAGE ATTACHMENTS link

[woodsedge]

I did that and can see it under "Current Attachments" on the "Manage Attachments" page but how do I then upload that attachment into this post? Thank you.

[FDibbins]

Looks to me like you already did

[woodsedge]

Happy accident! It's column B that I'm working on. Thank you.

[Sam Capricci]

well good luck Ford, I can't open it, I get an invalid extension error.

[woodsedge]

Well, shoot! I think your formula is gonna work, though. Thanks, again.

[FDibbins]

Try this...
=DATE(LEFT(TEXT(B2,"ddmm"),2),RIGHT(TEXT(B2,"ddmm"),2),1)

(I opened it just fine, BTW)

[woodsedge]

Thank you.

[FDibbins]

Happy to help

[Sam Capricci]

Yeah, I'm not sure why Ford but when I see one loaded with a .xls extension instead of .xlsx I always get an error that says excel cannot open the file... .xlsx because the file format r file extension is not valid.
if I try to save it and open it I still get the same message.
it happens every time I try one with old file extensions.

[FDibbins]

Do you have both .xls and xlsx associated with excel in the file association options?

[Sam Capricci]

I can open my own with the .xls extension, just not if it is on this forum.
I don't see it anywhere under file options.

[FDibbins]

I went into Windows Settings/Apps/Default Apps/Choose default apps by file type/scroll to almost the bottom and see if .xls is there and what it's associated with?