如何在Excel中的两个给定日期之间每2小时列出所有日期 [英] How to list all dates by every 2 hours between two given dates in Excel

问题描述

在我的工作中，我必须处理Excel表并收集时​​间范围之间的数据.

In my work I have to deal with Excel tables and gather data between time ranges.

到目前为止，我使用了以下VBA代码:

Till now I used the following VBA code:

Sub WriteDates()
'Updateby20150305
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId     = "KutoolsforExcel"
Set StartRng = Application.Selection
Set StartRng = Application.InputBox("Start Range (single cell):", xTitleId, StartRng.Address, Type: = 8)
Set EndRng   = Application.InputBox("End Range (single cell):", xTitleId, Type: = 8)
Set OutRng   = Application.InputBox("Out put to (single cell):", xTitleId, Type: = 8)
Set OutRng   = OutRng.Range("A1")
StartValue   = StartRng.Range("A1").Value
EndValue     = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
    Exit Sub
    End If
    ColIndex = 0
    For i = StartValue To EndValue
        OutRng.Offset(ColIndex, 0) = i
        ColIndex = ColIndex + 1
    Next
End Sub

但是此代码仅允许列出整天，而不是按小时列出.

But this code allows only to list whole days and not by hours.

例如，如果我输入介于01.01.2017和03.01.2017之间的日期范围=>以列出01.01.2017 02:00，然后01.01.2017 04:00依此类推...到02.01.2017 22:00.

For example if I enter date range between 01.01.2017 and 03.01.2017 => to list 01.01.2017 02:00, then 01.01.2017 04:00 and so on ... to 02.01.2017 22:00.

我尝试了几次以编辑此代码，但每次都将其破坏.我还尝试删除输入框，以使要从单元格B2和C2读取时间范围的代码，而在A17中将其作为输出，但同样没有成功.

I tried a few times to edit this code but I just broke it every time. I also tried to remove the Inputboxes so that the code to reads from Cells B2 and C2 the time range and in A17 to be the output but again no success.

我不是程序员，所以我尝试了一些有关VBA的知识，但我知道这是需要学习的东西.

I am not a programmer so I tried by reading a bit about VBA but I understood that is needed to learn a lot.

如果有人尝试过此方法或知道如何提供帮助，我将非常感激.

If someone has tried this or knows how to help I will be very grateful.

推荐答案

您使用的代码正在使用for循环"For i = StartValue To EndValue"来生成值，因此无处可输入2小时间隔.我的代码使用endDate和startDate通过将endDate-startDate乘以12来计算需要多少行.3小时后，您可以将for循环更改为while循环，以检查该值是否已达到endDate.

The code you have is using the for loop "For i = StartValue To EndValue" to generate the values so there is nowhere to enter your 2 hour interval. My code uses the endDate and startDate to calculate how many rows you will need by multiplying by endDate-startDate by 12. if the interval was not as easy to calculate e.g. 3 hours then you could change the for loop to a while loop that checks if the value has reached the endDate.

Sub WriteDates()
'Updateby20150305
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId = "KutoolsforExcel"
Set StartRng = Application.Selection
Set StartRng = Application.InputBox("Start Range (single cell):", xTitleId, StartRng.Address, Type:=8)
Set EndRng = Application.InputBox("End Range (single cell):", xTitleId, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
StartValue = StartRng.Range("A1").Value
EndValue = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
    Exit Sub
    End If
    ColIndex = 0
    intRows = (EndValue - StartValue) * 12 ' number of times you need to loop to get 2 hour intervals 24/2
    OutRng.Offset(0, 0) = StartValue ' put start value in the range
    OutRng.Offset(0, 0).NumberFormat = "dd/mm/yyyy hh:mm" 'set the format
    For RowIndex = 1 To intRows ' loop from 1 to intRows
        OutRng.Offset(RowIndex, 0) = OutRng.Offset(RowIndex - 1, 0) + CDate("02:00:00") 'put the value above + 2 hours
        OutRng.Offset(RowIndex, 0).NumberFormat = "dd/mm/yyyy hh:mm" ' set the format so that you can see the hours
    Next
End Sub

您也可以在excel中使用公式.将持续时间放入单元格A1(02:00)中，然后将开始日期放入B1(2017年2月1日)，将结束日期放入B2(2017年3月1日)，然后在B6中输入= B1并在B7中输入= ERROR(IF(B6 + $ A $ 1< = $ B $ 2，B6 + $ A $ 1，")，"))会自动将B7向下填充到您认为需要列表或更多以确保安全的程度.现在，当您更改A1，B1或B2中的任何内容时，您的列表将自动更新.

You can also use a formula in excel. Put your duration in cell A1 (02:00) then put your start date in B1 (01/02/2017) and your end date in B2 (01/03/2017) then in B6 enter =B1 and in B7 =IFERROR(IF(B6+$A$1<=$B$2,B6+$A$1,""),"") autofill B7 down as far as you think you'll need for your list or much more to be safe. Now when you change anything in A1, B1 or B2, your list will automatically update.

这篇关于如何在Excel中的两个给定日期之间每2小时列出所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！