Enter Date Without Separators

  • I'd like to be able to enter a date in excel as just the characters without the dashes and have it correctly recognized -- such as 05142006 changes to 5/14/2006. I have a great deal of data to enter and it would go much faster if I could do it in this format. Is there a VBA script or function of excel that I could use for this? I've tried looking around on here a bit and in the program settings, but I'm stumped. Thanks!

  • Re: Enter Date Without Dashes?


    I found something that works from the date and time entry page mentioned in the help section, but I can only get it to work for two ranges.


    It uses this statement:
    If Application.Intersect(Target, Range("E2:R2")) Is Nothing Then


    I figured out how to add a second set of ranges by adding it in with a comma ("E2:R2","E8:R8",)) but it doesn't seem to work for a third set. How can I include additional ranges in this statement, because otherwise the rest of the script works perfectly.

  • Re: Enter Date Without Dashes?


    TRY SOMETHING SIMPLE FIRST!!!!


    Try this. Enter all your dates and then format the cells the way you want them. Its really easy, just select the cells you want to format and then go to Format -> Cells. In the Format cells window click on "custom" in the category field. Then change the type in the type field. Excel recognizes m as month and d as day and y as year. So you want to enter "mmddyyyy" the example that they use is mm/dd/yyyy...but you don't want those slashes right....so just take them out.


    I hope this works for you.
    Good luck,
    Dave

  • Re: Enter Date Without Dashes?


    Sorry, I might not have explained myself quite so well.


    I want to be able to enter "032404" into the date field and have Excel recognize it as 03/24/04. If I enter 032404 into a cell it recognizes it as 9/18/1988. I'm not so concerned how Excel displays the date after I enter it, so much that it recognizes it as the date I intended.


    The script I got works really well and I can modify it for two sets of date cells I want to use, but I can't seem to get it to work for three sets of date cells.

  • Re: Enter Date Without Dashes?


    Ok....Here is a brute force approach using formulas. Please, please, please, Excel masters don't scoff at me. It works, its just messy :P


    If your Number value is in ddmmyy format in A1 here is an output in B1
    =CONCATENATE((LEFT(A1,2)),"/",(RIGHT(LEFT(A1,4),2)),"/",(RIGHT(LEFT(A1,6),2)))
    Make sure that A1 has a text format applied to it. That way the 0 in "032404" won't be chopped off.


    Of course you'll need two use two cells for this...but hey, if you really need it...


    Dave

  • Re: Enter Date Without Dashes?


    Custom Format: 00\/00\/0000


    This will allow you to enter "05142006"; the actual cell will lose the preceeding zero, but it will display as "05/14/2006"



    edit: your latest post says you want to enter "032404" so that would need a custom format of: 00\/00\/00

  • Re: Enter Date Without Dashes?


    I should have mentioned that this was an extension from a previous post and I needed the date field to not only display in a date format, but I needed it to remain a date field and not a text field, because I was using a worksheet made up for me that actually reads the field to determine a corresponding mileage amount based on the date.


    Anyhow, I figured out how to properly format the range command to include as many as I want! Thanks for all of your help everyone! You people on here are geniuses.

  • Re: Enter Date Without Separators


    After looking over the formula I posted, I thought of a way to both simplify it and keep the date format in the output cell.


    Here is the new formula:
    =DATE(MID(A1,5,2),MID(A1,1,2),MID(A1,3,2))
    A1 still contains the date in text format "032404"
    B1 now contains the date value "24-Mar-04"


    Now you can use column B as your "date field"


    Good luck,
    Dave

  • Re: Enter Date Without Separators


    Thanks again! That is quite similar to the VBS script I actually used. That worked the best too, since Excel still needs to be able to recognize it as a date in order to look up the corresponding mileage on the table. I've got everything just about perfect now -- it will make my job much easier! I will post a copy of in case anyone wants to use it for a similar task. I just need to work on the running total at the bottom, as Excel is summing it with all the decimal points and I can't get TRUNC or ROUND to work right based on how I've done it, but that's a whole other thread :spin:


    If you want to see how I specifically did the date, you'll want to check out the VBS code. Here is the site I gleaned it from: http://www.cpearson.com/excel/DateTimeEntry.htm

  • Re: Enter Date Without Separators


    But won't that change $2.72 into $2.00? My issue is that when I use decimals for my units (like 5.3 miles), it adds up the total using all the decimal points in the POV field, instead of adding it up based on just two decimals. I thought I could get around it by changing the POV cell formula into
    =ROUND(IF(E5<>"",E5*E3,""),2)
    which works, but only if all the cells with that formula in it are filled it (it displays the #VALUE! error otherwise) and I need a running total regardless of whether the cells are filled or not. I'm going to keep tinkering with it. This is pretty fun actually!

  • Re: Enter Date Without Separators


    Quote

    --------------------------------------------------------------------------------


    But won't that change $2.72 into $2.00?

    Yes. Your question was ambigous and vague.


    Start a new thread without assuming we can read your mind and stop using code tags on formulas.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!