Highline Excel Class 12: DATE functions & Formulas

[prudhvirajreddyable]

amazing video buddy keep it up

[ExcelIsFun]

@prudhvirajreddyable , I am glad that the vids help!

[mrkcueto7]

Good Day,How do i make a warning function with color on expiration date?Ex..it's color blue when its new,and red color 1 day before it expire...Tnx..

[ExcelIsFun]

@mrkcueto7 port Q to:

mrexcel [dot] com/forum

[mainengv]

How do I create a function that will allow me to write the the month, day, and year without excel automatically converting into its short version?

For example:

March 20011 is converted into March-11

March 23, 2011 is converted to March-11

Please help. Thanks so much!

[ExcelIsFun]

Try posting question to this site:

mrexcel[dot]com/forum

This is THE best Excel question site.

[dhadad82]

What a wonderful project Thank you!!!

daniel

[ExcelIsFun]

I am glad that it helped!

[qodir]

GREAT THANK YOU!

[ExcelIsFun]

You are welcome!

[firasathh]

Thanks a lot... U r simply a genius. it worked accurately with exact figures.

I will appreciate, if you could please elaborate little bit and provide some more details about how you have calculated the numbers entered in the formula. The calculation part is is little bit confusing for me.

[ExcelIsFun]

It is all about time in Excel. See this video about Time:

Excel Magic Trick 501: Excel Time Format & Calculations (10 Examples)

The RANSBETWEEN and /2400 gives random numbers between 0 and 8/24 (decimal representing proportion of 1 24 hour day). The ROUND is because occassionally you get a small decimal above 8 hours and I wanted to avoid that.

[firasathh]

Hi,

Your videos are marvelous... I'm like addicted to it.... you are doing a very good service to whole world by posting such a informative videos...

Can you please advise, how can i add random time to any date... I have a list of something around 7000 dates and I would like to add random hours, but less then 8, to all the dates... I have tried using RANDBETWEEN function, but it is not working. The date format I'm using is dd/mm/yyyy hh:mm AM/PM. Is there any good way to achieve it?

[ExcelIsFun]

If the dates are in column A starting in cell A1, then a formula like this in cell b1 and copy down would randomly add 0 to 8 hours:

=A1+ROUND(RANDBETWEEN(0,800)/2­400,5)

[aman1245singh]

Thanks for the videos again :)

[ExcelIsFun]

I am glad that the videos are helpful!

[jagiter]

thanx so much. just what i needed for the EDATE :)

[ExcelIsFun]

I am glad that it helps!

[thuynguyenable]

Dear Excellisfun,

Can you help me,please?

My date is 2/19/2010 in A3 cell

When I use =Day(A3), it give me 19. It's OK.

But when I use=Day(A3)+2, It gives me 1/21/1900. I thought it would give me No.21, two days after Feb 19.

Can you show me where I am wrong with the formula?

Thanks.

[ExcelIsFun]

There is nothing wrong with your formula. The number 21 is in the cell with Date Formatting. Apply General Number with:

Ctrl + Shift + ~

and you will remove Date format and see 21.

Number format is a facade. This is very important to know if you are going to advanced in your Excel expertise. See this video:

Excel Magic Trick 450: Number Formatting Abbreviated Notes (House Paint or Halloween Mask)

[bogdanakice]

in my Excel 2003, EDATE works just fine. It doesn't point that it would be a formula, but it does exact the same thing like in Excel 2007. Curious...

[Strftw]

cheers, that was helpful.

one thing i don't quite understand is, say im entering things and i want to just add one day onto a following day - how can i do this... something like typing +1?

thanks

[ExcelIsFun]

Yes that will work!

+ 1 works becasue a date is a serial number.

[secoja]

i have office 2003 and EDATE doesnt appear!

[ExcelIsFun]

Go to Tools Menu, Add-ins, then add the Data Analysis Toolpak.

[secoja]

Thanks!!

[ExcelIsFun]

You are welcome!

[soulsaver04]

dear excelisfun

the computer i hav in my office right now is not good and sometimes a simple copy paste does not work. i have problem using vlookup so what i did was to delete columns just to make the number of table array small. 2 columns only just to make the formula work. so i think the computer is the problem. i reviewing your videos maybe ive forgotten them

. i was just wondering if formulas does not work even on good condition pc. thanks

by the way im still using excel 2003. thanks

[soulsaver04]

hello excel,

may i ask if its normal for formulas not to work on really big spreadsheets?? is it common??

do i have to check always if my formulas actually work

[ExcelIsFun]

Dear soulsaver04,

Excel almost never makes math or logic errors. Formulas don't work right, when the person who made them made a mistake. In big spreadsheets sometimes formulas calculate very slowly. If you know how to get to my Playlists, I have a whole videos series about big spreadsheets named "Formula Efficiency Series".

What specifically is not working in your spreadsheet?

--excelisfun

[fantazticc]

Thanks for the video the EDATE was new for me. Very useful!

One thing that I think you could have mentioned about the DATE formula is how to use it together with the LEFT, MID and RIGHT formula to get a date that you can group etc. when working with data stored in databases. Many databases store them like this YYYYMMDD.

Thanks again

[ExcelIsFun]

Dear fantazticc,

That is a great idea! I have a video on this topic, but it is at my college web site for my Busn 214 class, but it is in a different file format. I will put it on my list of videos to make for YouTube!

--excelisfun

[scottylad2]

Great series otherwise. Learned loads from these tutorials.

many thanks

[ExcelIsFun]

Dear scottylad2,

I am glad that you like the Series. Do you know how to find all my Excel video Series (Playlists)?

If not, watch this video (Search for this title, then watah it):

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

--excelisfun

[scottylad2]

I use the MOD function to calculate times that span 1 day into the next, IE a night worker who starts at 18:00 and ends shift at 02:00am. =MOD(later time minus earlier time,Divisor 1) but not really sure what the function is doing.

[ExcelIsFun]

Dear scottylad2,

I made a video where I explain why the MOD function works for time. Search for and watch this video:

Excel Magic Trick 286: MOD function & Time Calculations

and this one:

Excel Magic Trick 298: MOD function Formula for Total Time Worked

--excelisfun

[planiolro]

Awesome video as usually! However I was surprised that you didnt mention anything about calculating difference between two dates. Moreover about the setbacks of DATEDIF function which is not giving right results all the time. I know that Houdini from Mr. Excel message board offered a solution using that constant array. The concept of constant array is very interesting. Where this concept can be applied besides calculating difference between two dates would very interesting! Thank you!

[ExcelIsFun]

Dear planiolro,

This video is just Date functions, not Date Math. For Date Math see these:

Highline Excel Class 04: Style & Number Format & ROUND

Excel Magic Trick #151: DATEDIF function (between two dates)

Excel Magic Trick #12: Date Math!

But, planiolro, I think you have seen all those.

And then there is Houdini's DATEDIF substitute formula (so amazing), but you have to go to the Mr Excel Message Board for that one.

--excelisfun