Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date and time
I have a macro i need to run that fills in the date and time. For example I
have data in column A and i need to put the date and time in column B everywhere there is data in column A. Date and time format needs to be 1/17/2008 6:30:00 PM. In addition i need to add a 1 to column C when ever there is data in column A. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date and time
This macro should do what you want...
Sub ApplyDate() Dim X As Long Dim LastDataCell As Long LastDataCell = Cells(Rows.Count, 1).End(xlUp).Row For X = 1 To LastDataCell If Len(Cells(X, 1)) 0 And Len(Cells(X, 2)) = 0 Then Cells(X, 2).Value = Now Cells(X, 3).Value = 1 End If Next End Sub Just one note on it... if there is already a date in Colum B, this macro will leave it (and all other cells in the row) as is... it will only add dates to that data not already having a date. Rick "Daniel M" wrote in message ... I have a macro i need to run that fills in the date and time. For example I have data in column A and i need to put the date and time in column B everywhere there is data in column A. Date and time format needs to be 1/17/2008 6:30:00 PM. In addition i need to add a 1 to column C when ever there is data in column A. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date and time
Rick Thanks! It did ALMOST everything i need.
I changed one line to Cells(X, 2).Value = Format(Now(), "m/d/yyyy h:mm:ss AM/PM") but i cannot seem to get it to display the seconds and am/pm?! If i click on the cell the formula bar displays the ss and am/pm but not in the cells itself. also if i right click and do format cells it shows custom m/d/yyyy h:mm. Now if i add this Columns("B:B").Select Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM" before the rest of the macro, it works?! Any idea why the format doesnt work properly? Thanks. danielm. "Rick Rothstein (MVP - VB)" wrote: This macro should do what you want... Sub ApplyDate() Dim X As Long Dim LastDataCell As Long LastDataCell = Cells(Rows.Count, 1).End(xlUp).Row For X = 1 To LastDataCell If Len(Cells(X, 1)) 0 And Len(Cells(X, 2)) = 0 Then Cells(X, 2).Value = Now Cells(X, 3).Value = 1 End If Next End Sub Just one note on it... if there is already a date in Colum B, this macro will leave it (and all other cells in the row) as is... it will only add dates to that data not already having a date. Rick "Daniel M" wrote in message ... I have a macro i need to run that fills in the date and time. For example I have data in column A and i need to put the date and time in column B everywhere there is data in column A. Date and time format needs to be 1/17/2008 6:30:00 PM. In addition i need to add a 1 to column C when ever there is data in column A. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
date and time
It is apparently a display issue with Excel. Select any of the cells in
Column B that had a date filled in by my code (before you added your NumberFormat change)... if you look at the formula bar, you will see the cell has the seconds in it... it just seems that Excel doesn't display them by default. You can also see this if you manually enter in a date and time (with seconds)... the seconds are not displayed unless you Custom Format the cell(s) to show them. Rick "Daniel M" wrote in message ... Rick Thanks! It did ALMOST everything i need. I changed one line to Cells(X, 2).Value = Format(Now(), "m/d/yyyy h:mm:ss AM/PM") but i cannot seem to get it to display the seconds and am/pm?! If i click on the cell the formula bar displays the ss and am/pm but not in the cells itself. also if i right click and do format cells it shows custom m/d/yyyy h:mm. Now if i add this Columns("B:B").Select Selection.NumberFormat = "m/d/yyyy h:mm:ss AM/PM" before the rest of the macro, it works?! Any idea why the format doesnt work properly? Thanks. danielm. "Rick Rothstein (MVP - VB)" wrote: This macro should do what you want... Sub ApplyDate() Dim X As Long Dim LastDataCell As Long LastDataCell = Cells(Rows.Count, 1).End(xlUp).Row For X = 1 To LastDataCell If Len(Cells(X, 1)) 0 And Len(Cells(X, 2)) = 0 Then Cells(X, 2).Value = Now Cells(X, 3).Value = 1 End If Next End Sub Just one note on it... if there is already a date in Colum B, this macro will leave it (and all other cells in the row) as is... it will only add dates to that data not already having a date. Rick "Daniel M" wrote in message ... I have a macro i need to run that fills in the date and time. For example I have data in column A and i need to put the date and time in column B everywhere there is data in column A. Date and time format needs to be 1/17/2008 6:30:00 PM. In addition i need to add a 1 to column C when ever there is data in column A. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting text format of time/date into Excel time/date for subtr | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Worksheet Functions |