Excel Magic Trick 1457: Text or Number Date / Times in Functions & Direct Operations (10 Examples)

Learn about how different functions in Excel and Direct Operations interact and interpret Date and Time Values with the different Data Types: Number or Text. Learn how the Functions: SUMIFS, COUNTIFS, COUNT, SUM, EOMONTH, MROUND, DATE and SUMPRODUCT treat Text Dates & Number Dates and Text Times & Number Times. Learn that Direct Operations treat Text Dates and Numbers dates differently and that many Excel Functions treat Text and Number Dates/Times as equivalent.

[dejanjovovic]

It's unbelievable that your videos are available here for free. It 's a miracle :D
With many many great videos here, we can literary acquire new excel skills almost every day. Keep up with good work, we are all grateful for it!

[bagnon]

I would be surprised if this was news for most viewers here, but for those who don’t know, one trick I use to convert text dates to real dates is to either copy and paste into notepad and back, or use the text-to-columns feature and choose fixed width so it stays in one column. The fixed width trick also works well for macros to fix dates and even numbers that were input as text.

[pmsocho]

Every Excel user should watch this video or know that stuff!

[ErikG]

Mike, another great video... I haven't used Excel help in a long time. It is so much better to go to Excell-is-fun and type in the function and see how you use it. Much more helpful. Thanks for all your help over the years.

[DougHExcel]

Wow...that's a LOT of great examples! Thanks Mike.

[VSP]

Excellent, as always. Very useful. Thank you Mike

[akhilmohansamantaray]

Mike, You r awesome..Lucky to have you my online mentor

[johnborg]

Very Helpful !!! By the way, you don't have to thank me for my support cause in actual fact you are supporting me to learn more and more from you :) God Bless you Mike.

[tanababa]

Hi Mike, Thank you for the clarification of the dates test/number scenarios.
Beautifully explained...as always :-)
Tamir

[entertainmentgalaxy]

Many thanks for sharing beautiful video as always.... One thing i want to know, how to handle text format date in PT which you have highlighted in example no 10...
Once again thanks for sharing this knowledge.
GBU

[rrrprogram]

Ye... First comment .. as usual.... Insightful....

[Shaked]

Hi I have a question regarding the IF formula, when I am using the formula to find empty cell for example: how do I write to correct value in the False array so it won't change the current cell value if it's not blank ?

[BillSzysz]

Good job, Mike !!! Many important things. Thanks a lot :-)

[barttitulaerexcelbart]

This was a cool video Mike. I always converted text with =VALUE or something. Amazing to see the MROUND function !!
Although it is difficult to remember all examples, message is clear: there are lot of functions the can interpret text as values. Just try it!
As for the Pivot Table problem: I once downloaded a file with date fields. All cells looked like data fields, they were formatted as data fields, but still grouping was not possible! Solution: copy all the data fields with 1 (paste special). et voila !

[dyaa_art]

Hello mike,
Thanks for the amazing videos.
I need your help on how to do one chart for tow columns where the difference between numbers is huge, as an example:
1st column has the Revenue in "millions"
2nd column has the Revenue Loss in "thousnds"
One chart/diagram for the tow


Thanks in advance. ..

[darshanshah]

u r great...can you please make videos on outlook & word ?

[Strobinski]

Interesting that SUMIFS doesn't distinguish between text and number ... - I like the SUMPRODUCT-Version (as well as all the other examples) :-)

Is --(...) faster than N(...)?

[parshurambhave]

The answers in the Sumproduct function are different. they should be 80:80:80:20 but are 60:60:60:20

[JonathanExcels]

Interesting. I would have build the formula in O17 as same answer as yours. not any easier or better. just different. if you wanted to make it deal with the text then perhaps which gives me 80 as the answer

[stevemorris]

Worth noting that VLOOKUPS don't play nice with numbers vs text...I see people getting stumped a lot by this one!