Date

10/24/2015:

- I have a scenario where I need to convert the date to the first of the month

Date(Month(<cell>,Year<cell>,1) = this will convert the date to first of every month.

 --- Month(serial_no) = gives the month of serial no. in numerics

---- Year and Day does the same

---Date function = produces date with inputs year, month and day i,e  Date(<year>,<month>,<day>)

  What evervalue you put in the year, month and day place, value displayed would be date format and as per the value.



1/22/2016:

- Lets suppose if you need date in a specific format, do this:

= Month(date) & "/" & Day(date) & "/" & Year(date)

if you just put  =Month(date)/Day(date)/Year(date)  -- it will divide the month by day and a result by year. So & is must.


Use DateValue method: Use this method to overcome issues with Date property of the value you enter into the Excel.

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008. Remember, though, that your computer's system date setting may cause the results of a DATEVALUE function to vary from this example.

Formula	Description	Result
=DATEVALUE("8/22/2011")	Serial number of a date entered as text.	40777
=DATEVALUE("22-MAY-2011")	Serial number of a date entered as text.	40685
=DATEVALUE("2011/02/23")	Serial number of a date entered as text.	40597
=DATEVALUE("5-JUL")	Serial number of a date entered as text, using the 1900 date system, and assuming the computer's built-in clock returns 2011 as the current year.	39634
=DATEVALUE(A2 & "/" & A3 & "/" & A4)	Serial number of a date created by combining the values in cells A2, A3, and A4.	40850