Adding Time to a Date
 

Hi,

I'm having problems with Time and Day issues.
My problems is that i would like to add Time intervals, and when it
passes a day, that the date is incremented and filled into the same
row (cell Ax) where the time pass midnight.

I have :
Startdate in cell C2
Starttime in C3
Offset time 1 in C7 (1:40:00)
Offset time2 in C8 (1:40:00)
Offset time 3 in C9 (00:10:00)

In cell C14 add i the Starttime + Offsettime1
in cell C15 add i the value of C14 + Offsettime2
in cell C15 add i the value of C15 + offsettime3

In cell C16 add i the value of C14 + Offsettime1
in cell C17 add i the value of C16 + Offsettime2
in cell C18 add i the value of C17 + offsettime3

and so on until the end of cell C48

This works without problems.
But at a certain moment, i will surpass 'midnight'
what i now want to do is, when i pass midnight, that i add 1 day to
the startdate and place that in cell Ax, the same row where the time
pass midnight.

I did try several things, but can't solve that problem.
The results are weird, and the formatting doesn't work either how i
expect it. I only want to place the date in cell Ax, but there appears
the date AND time in cellAx, even i use the Selection.NumberFormat =
"dd/mmm/yy" to format the cell into day,month,year.

Here's the code i use:
hint: i use dd-mm-yyyy as input in cell C2, and hh:mm:ss for cell C3
(starttime)
Sub FillDate()
'
Dim Date1 As Date
Dim Time1 As Date
Dim cntr1 As Integer
Dim cntr2 As Integer
'
Date1 = Range("C2").Value
Range("a14").Select
While cntr2 < 9
While cntr1 < 3
Time1 = Selection.Offset(0, 2).Value
If Selection.Offset(0, 2).Value 1 Then
Time1 = Time1 - 1
Debug.Print Time1, Selection.Offset(0, 2).Value
Date1 = Date1 + 1
End If
ActiveCell.Value = Date1 & " " & Time1
Selection.NumberFormat = "dd/mmm/yy"
cntr1 = cntr1 + 1
Selection.Offset(1, 0).Select
Wend
cntr1 = 0
Selection.Offset(1, 0).Select
cntr2 = cntr2 + 1
Wend
End Sub

any help welcome.

Ludo

Adding Time to a Date
 

the question is "How did you put the date into the cells". If you want to
get your dates consistent use date (force time to midnight) not now()
(includes present time). You can force a date to be midnight by using INT
Int(Startdate). Using format only changes the way a time is displayed it
doesn't truncated the hours and minutes line INT would do. Midnight is
equivalent to zero hours, minutes, and seconds.

"Ludo" wrote:

Hi,

I'm having problems with Time and Day issues.
My problems is that i would like to add Time intervals, and when it
passes a day, that the date is incremented and filled into the same
row (cell Ax) where the time pass midnight.

I have :
Startdate in cell C2
Starttime in C3
Offset time 1 in C7 (1:40:00)
Offset time2 in C8 (1:40:00)
Offset time 3 in C9 (00:10:00)

In cell C14 add i the Starttime + Offsettime1
in cell C15 add i the value of C14 + Offsettime2
in cell C15 add i the value of C15 + offsettime3

In cell C16 add i the value of C14 + Offsettime1
in cell C17 add i the value of C16 + Offsettime2
in cell C18 add i the value of C17 + offsettime3

and so on until the end of cell C48

This works without problems.
But at a certain moment, i will surpass 'midnight'
what i now want to do is, when i pass midnight, that i add 1 day to
the startdate and place that in cell Ax, the same row where the time
pass midnight.

I did try several things, but can't solve that problem.
The results are weird, and the formatting doesn't work either how i
expect it. I only want to place the date in cell Ax, but there appears
the date AND time in cellAx, even i use the Selection.NumberFormat =
"dd/mmm/yy" to format the cell into day,month,year.

Here's the code i use:
hint: i use dd-mm-yyyy as input in cell C2, and hh:mm:ss for cell C3
(starttime)
Sub FillDate()
'
Dim Date1 As Date
Dim Time1 As Date
Dim cntr1 As Integer
Dim cntr2 As Integer
'
Date1 = Range("C2").Value
Range("a14").Select
While cntr2 < 9
While cntr1 < 3
Time1 = Selection.Offset(0, 2).Value
If Selection.Offset(0, 2).Value 1 Then
Time1 = Time1 - 1
Debug.Print Time1, Selection.Offset(0, 2).Value
Date1 = Date1 + 1
End If
ActiveCell.Value = Date1 & " " & Time1
Selection.NumberFormat = "dd/mmm/yy"
cntr1 = cntr1 + 1
Selection.Offset(1, 0).Select
Wend
cntr1 = 0
Selection.Offset(1, 0).Select
cntr2 = cntr2 + 1
Wend
End Sub

any help welcome.

Ludo