I need Help with format date and data

[Erays]

I need an if statement if Cell D9 = 010000 then 000000 and if date2 has date entered then for example 09/25/05 would = 092505

[MWE]

I need an if statement if Cell D9 = 010000 then 000000 and if date2 has date entered then for example 09/25/05 would = 092505

I am assuming you want VBA code for the above. It appears for the first item that you are working with numbers but things might best be done with string variables. So, consider something like this:

if Range("D9").Text = "010000" then Range("D9").Text = "000000"

alternatively:

if Cells(9,4).Text = "010000" then Cells(9,4) = "000000"

For the 2nd case, I assume that Date2 is of type Date, so you do not want to stuff numers or text into Date2. You might want to consider:

Dim Date2     as Date
Dim strDate2 as string
if Date2 IsDate(Date2) = true then strDate2 = Format(Date2,"mmddyy")

a little test program:

Sub Test_Date2()
    Dim Date2     As Date
    Dim strDate2 As String
Date2 = Cells(1, 1)
    If IsDate(Date2) = True Then strDate2 = Format(Date2, "mmddyy")
    MsgBox Date2 & vbTab & strDate2
End Sub

[Erays]

Actually I need it to be aformula

[MWE]

Actually I need it to be aformula

OK, the first is pretty simple.

In, say, Cell D10, enter

=IF(D9="010000","000000","something else")

The 2nd is more difficult as one needs to ensure that the data entered is a date. So, this is brute force and ugly, but it works. Assuming the "date cell" is D13, enter

=IF(ISERROR(DATEVALUE(TEXT(D13,"dd-mmm-yyyy"))),"something else",TEXT(D13,"mmddyy"))

into some other cell

I am sure that others with more skill in ugly formulas will be able to improve on the 2nd forumula

[Zack Barresse]

Personally, I like to take the approach such as this: HERE .. when using worksheet formulas to check for a valid Excel recognized date. As there isn't a native function that does this, you can use a VBA UDF to simulate it.

I wouldn't say MWE's method is wrong, not by any means. I, personally, try to stay away from doubling the formulas though. I would try a nested IF, something like such ...

=IF(D9="010000","000000",IF(IsADate(D10),TEXT(D10,"MMDDYY"),0))

This is assuming that your 'date2' is in D10.

[MWE]

Personally, I like to take the approach such as this: HERE .. when using worksheet formulas to check for a valid Excel recognized date. As there isn't a native function that does this, you can use a VBA UDF to simulate it.

I wouldn't say MWE's method is wrong, not by any means. I, personally, try to stay away from doubling the formulas though. I would try a nested IF, something like such ...

=IF(D9="010000","000000",IF(IsADate(D10),TEXT(D10,"MMDDYY"),0))

This is assuming that your 'date2' is in D10.

I agree that the approach suggested (IsADate) solves the problem of no "IsDate" in Excel. I should have considered that. My attempt was to find a way around Excel's native shortcomings (and I ended up doing precisely what I suggest others do not do, i.e., incrementally build a nested mess)

I did not assume that erays wanted a cascaded solution (in pcode:

if D9 = "010000" then
xlcell = "000000"
Elseif 
IsDate(D10) = true then xlcell = Format(D10,"mmddyy")
end if

but now that I reread his original post, that could be the case.

[Zack Barresse]

I agree that some final clarification would be useful.

Erays?

[Erays]

The underlined portion has error when I put it in module

If D9 = "010000" Then
    xlcell = "000000"
ElseIf
    IsDate(D10) = True Then xlcell = Format(D10,"mmddyy")
End If

[Zack Barresse]

Actually I need it to be aformula

.. now I'm confused. Formula or VBA?

[Erays]

Actually I need it to be a formula but if it can be done with vba that will work just as well
This is my problem when the user puts in no date it is formated mmddyy and yeilds 010000 if he puts in date it yeilds 091605 if there is no date input i need it to read 000000 .

Thank you for all your help!

[MWE]

Actually I need it to be a formula but if it can be done with vba that will work just as well
This is my problem when the user puts in no date it is formated mmddyy and yeilds 010000 if he puts in date it yeilds 091605 if there is no date input i need it to read 000000 .

Thank you for all your help!

erays: well, it sounds like you were able to solve the problem and at least now it is reasonably clear what you wanted to do. I thought initially that you were playing with binary reps.

In the future, it will help everyone if you add a little background to the problem so those trying to assist will be better able to understand and the ping-pong of replies and clarifications can be minimized (and you get an answer much more quickly). Also, I have found that when I spend a few more minutes writing down my problem and explaining what I am trying to do, my understanding of my predicament improves and I can often solve the problem myself.

[Erays]

=IF(ISERROR(DATEVALUE(TEXT(D13,"dd-mmm-yyyy"))),"000000",TEXT(D13,"mmddyy"))

Fixed my problem Thank you to all who helped