Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
I'm wondering if there is a way with VBA to correc dates that look like this
(all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
If you can have both 10/5/2009 and 05/10/2009 on the same sheet (column?),
then no, a macro will not be able to figure out (on its own) that May was meant for both. You could write a macro that would ask you for the month and have the code use that information to straighten things out, but this would require you to select **all** the May dates manually before running the macro. Would that be an acceptable solution? Also, are these values formatted as Text or do they show as real Excel Dates in the cells? -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
Alfredo, missed few points here.
1. This will work only if ColA dates are in excel date format. 2. Once you apply the formula format Col B to a date format of your choice. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: If you are sure that all the dates are of month May try the below formula (not VBA code) in the column near to that... Suppose you have the below data in Col A in B1 enter the formula and copy down as required =DATE(YEAR(A1),5,IF(MONTH(A1)<5,MONTH(A1),DAY(A1) )) If this post helps click Yes --------------- Jacob Skaria "Alfredo_CPA" wrote: I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
I know for sure the month is May as I receive a monthly file with just one
month data, but for some reason I Ireceive it with the dates in all the ways I decribed in my original posting i.e. next month I will have the same problem with Jun data (06/01/09, 02/06/09, 3/06/09, etc - all will be June dates) Thanks "Rick Rothstein" wrote: If you can have both 10/5/2009 and 05/10/2009 on the same sheet (column?), then no, a macro will not be able to figure out (on its own) that May was meant for both. You could write a macro that would ask you for the month and have the code use that information to straighten things out, but this would require you to select **all** the May dates manually before running the macro. Would that be an acceptable solution? Also, are these values formatted as Text or do they show as real Excel Dates in the cells? -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
Give this macro a try...
Sub ConvertDatesToStandardFormat() Dim D As Date Dim C As Range Dim Answer As String Answer = InputBox("What month number should the dates be?", "Get Month") If Answer < "" Then If IsNumeric(Answer) Then For Each C In Selection If C.Value < "" Then D = CDate(C.Value) If Month(D) < Answer Then D = DateSerial(Year(D), Day(D), Month(D)) End If C.NumberFormat = "General" C.Value = D End If Next End If End If End Sub The macro starts off by asking you for the month **number** that the months should be and then changes all entries (whether they are text or real dates to start off with) into real dates, so you can format the column as you wish (or, if you tell me what format the dates should be displayed as, I can make the macro do the formatting automatically). Note: The macro will be fooled by 2-digit years which are not at the end of a text entry. For example, if this is in a cell formatted as Text... 09/05/07 where the 09 is meant to be the year, the macro will not be able to determine that. -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I know for sure the month is May as I receive a monthly file with just one month data, but for some reason I Ireceive it with the dates in all the ways I decribed in my original posting i.e. next month I will have the same problem with Jun data (06/01/09, 02/06/09, 3/06/09, etc - all will be June dates) Thanks "Rick Rothstein" wrote: If you can have both 10/5/2009 and 05/10/2009 on the same sheet (column?), then no, a macro will not be able to figure out (on its own) that May was meant for both. You could write a macro that would ask you for the month and have the code use that information to straighten things out, but this would require you to select **all** the May dates manually before running the macro. Would that be an acceptable solution? Also, are these values formatted as Text or do they show as real Excel Dates in the cells? -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
I like the idea. I'm trying to put the idea in VBA now.
I have this so far (I will loop it for selected range once it works): MyDate = DateValue(Range("a7")) MyMonth = Month(DateValue(Range("a7"))) MyDay = Day(DateValue(Range("a7"))) MyYear = Year(DateValue(Range("a7"))) MyRealMonth = InputBox("Enter the month you are working with", "MONTH") If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else MyRealDay = MyDay ActiveCell.Value = MyRealMonth & "/" & MyRealDay & "/" & MyYear This part of my code is not working. I'm having a problem with the if to asign the day: If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else MyRealDay = MyDay Thanks "Jacob Skaria" wrote: Alfredo, missed few points here. 1. This will work only if ColA dates are in excel date format. 2. Once you apply the formula format Col B to a date format of your choice. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: If you are sure that all the dates are of month May try the below formula (not VBA code) in the column near to that... Suppose you have the below data in Col A in B1 enter the formula and copy down as required =DATE(YEAR(A1),5,IF(MONTH(A1)<5,MONTH(A1),DAY(A1) )) If this post helps click Yes --------------- Jacob Skaria "Alfredo_CPA" wrote: I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
Thanks Rick ,it works perfect.
Just for my VBA training: will you please take a look of the code I wrote in my last reply to Jacob and let me know where is the issue? Thanks, I really appreciate your help "Rick Rothstein" wrote: Give this macro a try... Sub ConvertDatesToStandardFormat() Dim D As Date Dim C As Range Dim Answer As String Answer = InputBox("What month number should the dates be?", "Get Month") If Answer < "" Then If IsNumeric(Answer) Then For Each C In Selection If C.Value < "" Then D = CDate(C.Value) If Month(D) < Answer Then D = DateSerial(Year(D), Day(D), Month(D)) End If C.NumberFormat = "General" C.Value = D End If Next End If End If End Sub The macro starts off by asking you for the month **number** that the months should be and then changes all entries (whether they are text or real dates to start off with) into real dates, so you can format the column as you wish (or, if you tell me what format the dates should be displayed as, I can make the macro do the formatting automatically). Note: The macro will be fooled by 2-digit years which are not at the end of a text entry. For example, if this is in a cell formatted as Text... 09/05/07 where the 09 is meant to be the year, the macro will not be able to determine that. -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I know for sure the month is May as I receive a monthly file with just one month data, but for some reason I Ireceive it with the dates in all the ways I decribed in my original posting i.e. next month I will have the same problem with Jun data (06/01/09, 02/06/09, 3/06/09, etc - all will be June dates) Thanks "Rick Rothstein" wrote: If you can have both 10/5/2009 and 05/10/2009 on the same sheet (column?), then no, a macro will not be able to figure out (on its own) that May was meant for both. You could write a macro that would ask you for the month and have the code use that information to straighten things out, but this would require you to select **all** the May dates manually before running the macro. Would that be an acceptable solution? Also, are these values formatted as Text or do they show as real Excel Dates in the cells? -- Rick (MVP - Excel) "Alfredo_CPA" .(donotspam) wrote in message ... I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
Hi Alfredo
The reason why it was not working is basically the variable type mismatch. MyReal month from inpubox is returned as a string which you need to convert to numeric before converting....or you need to declaare the variables in the first place.....Anyway I have modified your code to work. Use IIF function() which reduce a bith of lines..... mydate = DateValue(Range("a7")) mymonth = CInt(Month(mydate)) myday = CInt(Day(mydate)) MyYear = Year(mydate) myrealmonth = CInt("0" & InputBox("Enter the month you are working with", "MONTH")) MyRealDate = DateSerial(MyYear, myrealmonth, IIf(mymonth < myrealmonth, mymonth, myday)) If this post helps click Yes --------------- Jacob Skaria "Alfredo_CPA" wrote: I like the idea. I'm trying to put the idea in VBA now. I have this so far (I will loop it for selected range once it works): MyDate = DateValue(Range("a7")) MyMonth = Month(DateValue(Range("a7"))) MyDay = Day(DateValue(Range("a7"))) MyYear = Year(DateValue(Range("a7"))) MyRealMonth = InputBox("Enter the month you are working with", "MONTH") If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else MyRealDay = MyDay ActiveCell.Value = MyRealMonth & "/" & MyRealDay & "/" & MyYear This part of my code is not working. I'm having a problem with the if to asign the day: If MyMonth < MyRealMonth Then MyRealDay = MyMonth Else MyRealDay = MyDay Thanks "Jacob Skaria" wrote: Alfredo, missed few points here. 1. This will work only if ColA dates are in excel date format. 2. Once you apply the formula format Col B to a date format of your choice. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: If you are sure that all the dates are of month May try the below formula (not VBA code) in the column near to that... Suppose you have the below data in Col A in B1 enter the formula and copy down as required =DATE(YEAR(A1),5,IF(MONTH(A1)<5,MONTH(A1),DAY(A1) )) If this post helps click Yes --------------- Jacob Skaria "Alfredo_CPA" wrote: I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating date
try this UDF
in the spreadsheet you'd enter =CorrectedDate(B6,5) where B6 is a date like your 5/10/2009 and 5 is the correct month --- means you can use this for june, july etc the answer will be an excel date, so format the cell appropriately like "dd-mmm-yy" or whatever Option Explicit Function CorrectedDate(text As String, mnth As Long) As Date ' input v1/v2/yyyy ' where either v1 or v2 is correct month Dim dte As Variant ' becomes a vector with 3 values dte = Split(text, "/") ' dte becomes a vector with 3 values If dte(0) = mnth Then ' text is mm/dd/yy CorrectedDate = DateSerial(dte(2), dte(0), dte(1)) Else ' text is dd/mm/yy CorrectedDate = DateSerial(dte(2), dte(1), dte(0)) End If End Function "Alfredo_CPA" .(donotspam) wrote in message ... I'm wondering if there is a way with VBA to correc dates that look like this (all of them are MAY dates). Is not a format issue as they look typed like that: 10/5/2009 1/05/2009 04/5/2009 1/5/09 5/10/09 05/10/2009 5/1/09 5/01/09 etc -- Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating date | Excel Programming | |||
Manipulating Date Using Dropdown | Excel Discussion (Misc queries) | |||
Manipulating dates | Excel Discussion (Misc queries) | |||
Manipulating dates (was Add 6 months to a date) | New Users to Excel | |||
Manipulating DOS from VBA | Excel Programming |