How to Display AM and PM Entries for Every Date in Excel
Question details
The user wants to automatically generate a sequence of dates where each day has two distinct entries (one for AM and one for PM) that fill across the worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a detailed daily timeline or schedule that tracks morning (AM) and afternoon/evening (PM) shifts or events for an entire month without manual data entry.
- Observed behavior
- The user needs a method to populate the dates automatically with alternating AM and PM increments across a row or column.
Ensure you have enough empty columns to the right of your starting cell to accommodate two entries per day for the entire month (up to 62 columns). Also, verify your system's regional date settings, as this affects how custom date formats are applied.
Use a 12-Hour Increment Formula with Custom Formatting
The most efficient way to generate alternating AM and PM entries is to add 0.5 (representing 12 hours) to the previous cell and apply a custom date format.
In Excel, dates are stored as serial numbers where 1 represents a full 24-hour day. By adding 0.5 to a date value, you are effectively adding 12 hours. This perfectly alternates the time between midnight (AM) and noon (PM).
Select your starting cell (for example, B4) and type the first date of the month, such as 1-Oct-2024.
In the next adjacent cell to the right (C4), enter the formula: =B4+0.5 and press Enter.
Click the fill handle (the small square at the bottom-right corner of cell C4) and drag it across the row to generate entries for the entire month.
Select the entire range of generated dates. Right-click the selection and choose 'Format Cells'. Navigate to the 'Number' tab, select 'Custom', and enter 'dd/mm/yyyy AM/PM' or 'mm/dd/yyyy AM/PM' into the Type box. Click OK.
Create Advanced Date Schedules Easily with WPS Spreadsheet
WPS Spreadsheet fully supports all standard Excel formulas and custom number formatting, making it effortless to build detailed AM/PM timelines. It is a free, lightweight, and highly compatible tool for all your data scheduling and tracking needs.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet or open your existing schedule document.
- 2. Enter the Increment Formula: Type your start date in the first cell. In the next cell, enter the formula =A1+0.5 (assuming your start date is in A1) to create the 12-hour increment, then drag to fill.
- 3. Format the Cells: Right-click the selected cells, choose 'Format Cells', navigate to 'Custom', and input your desired dd/mm/yyyy AM/PM format to display the results clearly.
Frequently Asked Questions
Why do my dates display as decimal numbers like 45200.5?
Excel stores dates and times as serial numbers. If you see a decimal number instead of a date, it means the cell is formatted as 'General' or 'Number'. To fix this, simply right-click the cells, select 'Format Cells', and apply a 'Date' or 'Custom' format.
Can I display the exact hour along with the AM/PM indicator?
Yes. In the Format Cells > Custom menu, change your format code to include hours and minutes. Using a format like 'dd/mm/yyyy hh:mm AM/PM' will display both the specific time (e.g., 12:00 AM) and the date in the cell.
How can I make the AM/PM entries fill down a column instead of across a row?
The logic is exactly the same. Instead of placing the formula in the next column to the right, put it in the cell immediately below your start date (e.g., if the start date is in B4, type =B4+0.5 in B5). Then, click and drag the fill handle down the column.
Is there a way to exclude weekends from the AM/PM timeline?
Excluding weekends while keeping 12-hour increments requires combining the WORKDAY function with time manipulation, which can be complex. A simpler alternative is to generate the full month first, apply a filter to the date row, and uncheck Saturdays and Sundays to hide them.
