File Submission: Formatting Dates (5 minutes)

Dive deeper into date formatting to learn common date errors and how to format dates correctly for the graduates file.

Resources

• Sample Graduates File (XLS)
• Submit a Graduates File Guide (DocX)
• Formatting and Submitting a Graduates File
• Common Graduates File Submission Errors

Transcript

Now let's format dates properly in the yyyymmdd format. This applies to cell E1 and columns I and L. For example, if a student's High School graduation date is June 1st 2022 it must be formatted as 20220601. Depending on how your SIS extracts date formats, it may affect the process you need to take to properly format them. If your dates are in mm/dd/yyyyy format, take the following steps. Highlight the column or cells of the columns to be formatted, right click and select format cells. You can also navigate here using the home ribbon. When the format cells window appears, select custom under the category field. In the type box, replace the type with yyyymmdd. Select okay. If your file is already formatted as yyyymmdd and you try to enter the date in that same format, it will cause hashtags to appear. If you have already formatted the cell as yyyymmdd, you must enter the dates as mm/dd/yyyy so all the dates are formatted correctly. Now let's discuss some Excel formatting date scenarios using a sample graduates file. Note that in column I many of the dates of birth are in different formats. Select the cell 1-jan-03. The formula bar shows 1/1/2003 which is how the date will display if we change the format to short date. Select the cell containing 20040409. Notice in the home bar that it is being stored as a number. We can change it to text, but if we change the value to short date, only hashtags will show. Select the cell containing 5/1/2005. Change the format to General or number and the value shown becomes 38473 which is now Excel will store it unless it is told it is a date. Let's highlight the entire column of dates and change it to yyyymmdd format. Notice that only a few cells change. You can address both cells I3 and I9 which are displaying hashtags simultaneously. Select I3, hold down control and select I9 and then change the format to text. Although the dates are now aligned left, they will still be read properly. Alternatively, you can re-enter the dates as mmddyyyy so the custom formatting will auto format these dates as needed. Let's do this for cells I5 and I11. The last thing to discuss here is that clicking and dragging a date from one row to another will cause each subsequent cell beneath it to add one day. Be cautious of this as you work with your graduation dates. For instance, if you click and drag the graduation date, each student will have a different graduation date. One way to address this is to copy and paste one graduation date into three consecutive cells, highlight those cells and then click and drag. This will ensure no days are added. There's another way to fill all detail rows with a single high school graduation date. Let's say you want to enter the same high school graduation date into rows 2 through 11 in column L. Start by selecting cell L2 then in the name box, which is located in the upper left corner of the file, change the value here to L2 to L11 and press enter. Type the graduation date, hold down control and press enter. You will see that the same value has been entered into rows 2 through 11 for column L. This concludes the dates formatting lesson.