Macro Help Date Time

[wpjensen]

Hi All,
Still Having problems with a macro to do the example below enable to get this to work properly as you can see the minutes won't reset at 60 and the hours won't change, let alone the days. In Column A Column B is what the decided result

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=131 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17><TABLE style="WIDTH: 201pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=268 x:str><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 102pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=136>Col A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 width=132>Col B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>14-03-2011 23:58:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">14-03-2011 23:58:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>14-03-2011 23:59:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">14-03-2011 23:59:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>14-03-2011 23:60:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">15-03-2011 00:00:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>14-03-2011 23:61:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">15-03-2011 00:01:00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>14-03-2011 23:62:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">15-03-2011 00:02:00</TD></TR></TBODY></TABLE></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Can anyone help here or know how to do this Please
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=131 x:str><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>Wayne</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>

 

[shg]

Could you explain what the numbers represent and what you're trying to do?

 

[AlphaFrog]

Try something like this...

    Range("A1:A10").ClearContents
    Range("A1:A10").NumberFormat = "dd-mm-yyyy hh:mm:ss"
    Range("A1").Value = DateValue("Mar 14, 2011") + TimeValue("11:55:00 PM")
    Range("A2").Value = DateValue("Mar 14, 2011") + TimeValue("11:56:00 PM")
    Range("A1:A2").AutoFill Destination:=Range("A1:A10")

 

[wpjensen]

Hi, Date and Time in Col A the corrected example in Col B

increased by the minute around 5000 cells to convert

Change the hour and days as go down the list

Wayne

 

[wpjensen]

Alfa Frog,
Your macro works good for the first 100 cells then goes haywire
any thoughts what might be going on

LOOKS LIKE IT ADDS A SECOND EVERY 100 CELLS how do you stop that ??????


Wayne

 

[AlphaFrog]

Show me your code.

 

[wpjensen]

Here is what I have

Sub CopyDownTry()
Range("H1:H430").ClearContents
Range("H1:H430").NumberFormat = "dd-mm-yyyy hh:mm:ss"
Range("H1").Value = DateValue("Mar 14, 2011") + TimeValue("10:55:00 PM")
Range("H2").Value = DateValue("Mar 14, 2011") + TimeValue("10:56:00 PM")
Range("H1:H2").AutoFill Destination:=Range("H1:H370")
End Sub


Not sure or how to post code

Wayne

 

[shg]

Try this:

Sub CopyDownTry()
    Dim sAdr As String
    
    With Range("H1:H430")
        sAdr = .Cells(1).Address
        .ClearContents
        .NumberFormat = "dd-mm-yyyy hh:mm:ss"
        .Formula = "=""Mar 14, 2011 10:55:00 PM"" + (row() - row(" & sAdr & ")) / 1440"
        .Value = .Value
    End With
End Sub

 

[AlphaFrog]

What shg4421 said. I was about to post something similar.

 

[wpjensen]

Thanks Guys for your help

shg4421 your the winner it works as required thanks very much I owe you one

Thanks again Wayne