Forum Discussion
Excel dates
Hello I have a quick question. I have data that I need to organize and I want to do it by the dates my employees work. But when I download the data it’s not formatted the way I need. For example, the downloaded data looks like this:
1/1 89
1/2. 97
1/6 90
1/8 89
There is no blanks on the days they don’t work. When I add more data from other sheets my days don’t always line up. So my question is how can I get the dates column to create spaces for the days in between? For example :
1/1. 87
1/2. 97
1/3.
1/4
1/5
1/6. 90
1/7
1/8 89
hopefully this makes sense. I need the days in between listed and them a c way to get the other days to format correctly according to the days still so that the correct data still moves with the correct date
Cto06 You can set up a few simple formulas as shown.
E2 finds the minimum date, E3 finds the maximum date.
D9 creates a sequence of dates from the minimum to maximum. You need to format the cells as date.
E9 uses XLOOKUP to find the date in the input list and bring over the amount. If the date is not found in the input list, it leaves the amount blank.
I hope that the point after 1/2. etc. is a typo.
Run this macro:
Sub InsertDates() Const firstrow = 1 ' first data row; change as needed Dim r As Long Dim lastrow As Long Application.ScreenUpdating = False lastrow = Range("A" & Rows.Count).End(xlUp).Row r = lastrow Do If Range("A" & r).Value > Range("A" & r - 1).Value + 1 Then Range("A" & r).EntireRow.Insert Range("A" & r).Value = Range("A" & r + 1).Value - 1 Else r = r - 1 End If Loop Until r = 1 Application.ScreenUpdating = False End Sub
