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.exceltraining101.com/p/tr...
📚 Excel Books & Tech Gear ➜ www.amazon.com/shop/dough
⚙️ Tools: Screencasting ➜ techsmith.z6rjha.net/5Qe53
⚙️ Tools: Microsoft Office ➜ microsoft.msafflnk.net/rKL0G
⚙️ Tools: TubeBuddy ➜ www.tubebuddy.com/et101
📝 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.buymeacoffee.com/dough
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~
Пікірлер: 46
Thanks for the valuable date info!!
That Text to Columns function is a MONSTER, y'all!!! Worked PERFECTLY - FIRST time [if ONLY I had known this YEARS ago!!!] - THANKS!!! 🙂
@DougHExcel
11 ай бұрын
Hi Spot ON, you're welcome!
This is really helpful, thank you very much
@DougHExcel
3 жыл бұрын
Glad it was helpful!
I have the longdate - Janury 3, 2021 but when I use the datevalue fuction it doesn't do anything
Very helpful! Thank you!
@DougHExcel
4 жыл бұрын
You're welcome!
This helped me sooo much thank you!!!
@DougHExcel
4 жыл бұрын
Glad it helped!
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!
Thanks Doug! This just solved an issue with some system generated reports that has been driving me crazy.
@DougHExcel
2 жыл бұрын
Glad it helped!
Many Thanks!
@DougHExcel
6 жыл бұрын
You're Welcome!
Thanks it worked!
@DougHExcel
2 жыл бұрын
Glad it helped!
It's not working in my computer.what should I do?
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
3 жыл бұрын
as long as they "match" yes
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)?
Beautiful Video, you are a genius
@DougHExcel
2 жыл бұрын
Thanks for the kind words!
Thank you so much
@DougHExcel
3 жыл бұрын
You’re welcome!
why it don't work on mine __-
Thank you Big Time!
@DougHExcel
3 жыл бұрын
You’re welcome!
Good post.
@DougHExcel
6 жыл бұрын
Thanks David S!
please, What is the reason(s) that makes/make the date in txt format in a cell?
@DougHExcel
2 жыл бұрын
Could be different things. One reason may be a space before or after.
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
7 жыл бұрын
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
4 жыл бұрын
@@DougHExcel do a video about it if you want
can you convert this one- 18-05-2018. Excel won't convert this!!!! Why?
@DougHExcel
3 жыл бұрын
it's seen as text, so pull them dd-mm-yyyy as separate columns and stitch it back to gether kzread.info/dash/bejne/fKOM16-tmrfeotY.html
I don't know if you'll see this comment after so many years, but thanks for the info man !
I love you
@DougHExcel
6 жыл бұрын
☺
Arlisa
@DougHExcel
3 жыл бұрын
Hi Bernard Mcdonald, thanks for the comment
A huge thanks bro for this video , u saved my life !!!!!!!!!!!!!! I was about to kill myself because of this fucking error !!!!!!!!!!!!!!
@DougHExcel
7 жыл бұрын
Glad it helped you out!