Converting Excel Dates – Most Excel workbooks will use dates in them at some point. This could be for example a sales invoice or order form to keep track of dates entered into the system. Date and time functions can be used to extract values such as a date range, or dates or times.
I have an example below to illustrate this worksheet functionality
- Date Product Sales Order
- 01/01/2015 Beanie Bag 100
- 01/01/2015 Beanie Bag Sub 100
- 02/01/2015 Beanie Bagoses 50
- 05/01/2015 attached to an open stock item
- 10/01/2015 Beanie renewable
This example shows you how dates are extracted from a list of dates on an Excel worksheet
The worksheet actions are used to navigate to a cell and then you want to extract the date.
Excel allows extracting of values in cells, however, date extracted are not always in date order. This is where the DATEDIF function comes in handy.
This formula will extract the date from the date list as long as the values are in the correct order. So if you wanted to know how many days it has gone since a date was entered into the system, you would use the formula with the date keyed in. So, if Sep 17th is entered on 01/01/2015 and the formula returns 19, you will get 19 as the value chosen from the list. The optional second argument is the number of days in the ahead returned in the AM stick Manufacturer field Converting Excel Dates.
So, the formula would be
=DATEDIF(A2:C4,1, dated flag)
This means to convert the values in cells A2 to C4, if they are not already present in the date list. The days in the first column are not considered, so if they don’t appear first in the list, with the date in the next cell, continue to apply the logic. So if it was Sep 17th, and the list had just arrived, continue to apply the logic to ensure the number of completed dates in the list is greater than the number of days old the data is looking.
The complete formula would look like below
=DATEDIF(A2:C4,1, DATEDIF(A2:D4,1, 1))
This literally breaks down into looking at the date before it and then looking at the number of days old in the relevant cell.
As a result of the logic test, if the list has not yet arrived at the date:
=DATEDIF(A2:C4,1, 0) & ” ” & DATEDIF(A2:D4,1, 1))
You must ensure that these two functions are always put into separate spreadsheets, as they can cause confusion. Better, perhaps, that you put these in the same workbook, and use the sheet’s formulas to ensure that only one of these two is active. That way, you can safely change the cell references without causing a problem.
The Sheet underneath the results should look something like below. The cells that are in grey are part of the formula, and are not part of the formula.
A2:C4=STEP(ROW(A2:C4),2,FALSE)A2:D4=STEP(ROW(A2:D4),2,FALSE)
I have found this type of formula particularly useful
Imagine you have a list of Product descriptors, and you want to have each product attribute copied into each row of the product attribute. This would give you a list of:
- Product Name:AM4
- Product Name: repetition(ram, school, installe, selling price)
- Product Name:━
- Product Name:¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯_2
- Product Name: installe
- Product Name: selling price
- Your formula would look like:
- ” complimentary|format|payload|am4″
A2:C4=STEP(ROW(A2:C4),2,FALSE)&” “&DATEDIF(A2:D4,A4,”2”)
Your result should look like below. Do you see any duplitions of the attributes?
YAY!!
That’s it. You have found out how to use DATEDIF formulas!!
We can do the same thing for our Employees as well, as the formula for the column to the left of our dependent column (compare with the Employees formula).
Let’s talk through the formula that you would use if you wanted to know which employees got promoted on 01/01/2015 and 01/02/2015.