DATES and TIME , if you are new to Excel and you have already tried using those format, I am pretty sure that you must have felt panicky at some sort of time.
It is quite obvious because when we don’t know the exact working of any system or process, we try to use it just by hit and trial method. It works sometimes and sometimes not.Here we are talking about Dates and Time in Excel. These are the tricky formats which we need frequently in our reports or charts.
Many times, we need to do operations on them. We need to put them in the conditions to trigger some event which makes it very important for us to learn the exact procedures to perform a task concerned with the dates and time.
In this article we would learn different tricks and methods to handle and manipulate Dates and Time formats so that they don’t mess up with our reports.
In this article we would learn about the trick to find out the SERIAL NUMBER of any date.
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.
GETTING THE DATE SERIAL NUMBER OF ANY DATE IN EXCEL
This might be the need for some specific case when we need to know the serial number of the Date in Excel.
We can accomplish this task in two ways. Using the FORMAT and using the function.
We know that the DATE is just a number for the Excel.
So if we have any date in the DATE FORMAT and if we convert the format to the GENERAL FORMAT, it’ll get converted into number directly. Similarly EXCEL provides us a direct function too for this task which is known as DATEVALUE.In the examples, we’ll try both the ways.
1. USING THE FORMAT
STEPS TO FIND OUT THE SERIAL NUMBER OF DATE BY FORMATTING CELLS
- Insert the date in any cell.
- As soon as the date is inserted the Excel would convert the format of the cells to DATE or CUSTOM [ For some formats which comes under custom’
- Go to HOME TAB and change the format under the NUMBER SECTION by choosing GENERAL.
- The cell would contain the serial number of the date.
2. USING THE FUNCTION
STEPS TO FIND OUT THE SERIAL NUMBER OF DATE BY USING DATEVALUE FUNCTION.
- Use the following function in the cell where serial number is needed.
- =DATEVALUE(“DATE”). It’ll return the SERIAL NUMBER of the date given.
- If the date is present in a cell, make sure the format of the cell is TEXT i.e. the DATEVALUE function would take the date as text as input.