Make Excel Recognize Dates with the DATEVALUE Function

Sometimes you get a file that has dates that don't seem to work like real dates (i.e., you can't perform calculations on it). Maybe those cells have dates that are really 'text' dates, not values. You want to represent these text dates into a proper Excel date (which is a serial number). It'll take the text string date and transform it without any VBA. This video show you how to changes those text dates to 'real' dates so you can perform calculations.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

[@spoton7683]

That Text to Columns function is a MONSTER, y'all!!! Worked PERFECTLY - FIRST time [if ONLY I had known this YEARS ago!!!] - THANKS!!! 🙂

[@DougHExcel]

Hi Spot ON, you're welcome!

[@excelisfun]

Thanks for the valuable date info!!

[@chantelleyurechuk9764]

Beautiful Video, you are a genius

[@DougHExcel]

Thanks for the kind words!

[@myronburkholder2499]

Thanks Doug! This just solved an issue with some system generated reports that has been driving me crazy.

[@DougHExcel]

Glad it helped!

[@美愛-w6j]

This is really helpful, thank you very much

[@DougHExcel]

Glad it was helpful!

[@akanksha4885]

Very helpful! Thank you!

[@DougHExcel]

You're welcome!

[@NeiJiaQuanBook]

Thanks it worked!

[@DougHExcel]

Glad it helped!

[@lol-pz1ni]

I have the longdate - Janury 3, 2021 but when I use the datevalue fuction it doesn't do anything

[@TheHellokittyluz]

This helped me sooo much thank you!!!

[@DougHExcel]

Glad it helped!

[@invenew]

please, What is the reason(s) that makes/make the date in txt format in a cell?

[@DougHExcel]

Could be different things. One reason may be a space before or after.

[@pawanpawar8466]

Thank you so much

[@DougHExcel]

You’re welcome!

[@gurijalasandeep2293]

It's not working in my computer.what should I do?

[@BlvcK_yt]

I don't know if you'll see this comment after so many years, but thanks for the info man !

[@MrLeoprovodnik]

Thank you Big Time!

[@DougHExcel]

You’re welcome!

[@WenSaiPanther]

How can you use DATEVALUE in a range while using a LOOKUP, VLOOKUP, or HLOOKUP, or other search functions?
suppose I am looking something from one column as 12/05/18 to look up something in a row but the searchable column has "Tuesday 12/05/18 6:50pm". can I use DATEVALUE, or anything to that range of fields?

[@DougHExcel]

as long as they "match" yes

[@ZAHIDHUSSAIN-ri5kg]

Many Thanks!

[@DougHExcel]

You're Welcome!

[@daliaarmada]

Hi Doug, how can I contact you, please? I need some help with an excel sheet I need to create for work and cannot find anything online close to. Thanks in advanced!

[@BLOODYHACKER]

why it don't work on mine __-

[@tomchicago]

my date value returns says "#value!", this is a system extraction from a massive CRM database. how do you fix this? it looks legit visually.

[@DougHExcel]

it might be that the function is looking for a number, but is doing calculation on text string. You may want to check out if the reference cells are strings or numbers.

[@oddnumber8149]

@@DougHExcel do a video about it if you want

[@fahmylina123]

The reported method does not work at all with a text like the one shown between brackets (3/20/2020 4:00:00 PM) and the similar texts downward in a column imported from a CSV file for a dataset of a personal weather station logger. I only know that the long column should have such texts increasing in each lower row by 12 minutes i.e. the next to the mentioned text should be (3/20/2020 4:12:00 PM). I've copied the whole column and an empty one next to it and copied a previous cell that contains a correct date and time and pasted it in the next row in that newly populated column and got numbers but cannot use them to make calculations using IF function to verify that there are no missed rows while importing the original form the data-logger, so I copied these numbers and pasted them as values in a third empty column. Now, I can verify the presence of missed rows using the IF function but only after comparing each cell in each row with the original text cells; something which is so tedious and prone to error. Anyway, I'm so astonished why Excel fails to correctly carry out the method you kindly report in this nice video to convert text into data and time. Should one first make several separate columns while in the conversion process and then stitch them together (something that will be tedious too)?

[@DavidS-np8jp]

Good post.

[@DougHExcel]

Thanks David S!

[@rockhamal]

can you convert this one- 18-05-2018. Excel won't convert this!!!! Why?

[@DougHExcel]

it's seen as text, so pull them dd-mm-yyyy as separate columns and stitch it back to gether
ua-cam.com/video/JoXuMtjTxrs/v-deo.html

[@victorcaypop736]

I love you

[@DougHExcel]

☺

[@malekferhi5029]

A huge thanks bro for this video , u saved my life !!!!!!!!!!!!!! I was about to kill myself because of this fucking error !!!!!!!!!!!!!!

[@DougHExcel]

Glad it helped you out!

[@sabarinathan6554]

im getting value error

[@Krisboy12New]

Sir please hindi me video bana dijye please sir

[@bernardmcdonald1215]

Arlisa

[@DougHExcel]

Hi Bernard Mcdonald, thanks for the comment