Date formatted cell using text

[data808]

Is there a way to force the user to type a specific format of a date but it has to be in text format. For example:

User types "Sep 05 2022" into cell and that is what reflects in the function bar as well without auto formatting into a date cell. Also can I use data validation to ensure that the date is typed in this format all the time?

Thanks.

 

[6StringJazzer]

You can format the cell as Text and anything typed into the cell will be text, and not converted to a date.

You can use data validation with a custom formula (in this example the cell is J6)

=AND(OR(LEFT(J6,3)={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}),MID(J6,4,1)=" ",ISNUMBER(VALUE(MID(J6,5,2))),MID(J6,7,1)=" ",ISNUMBER(VALUE(MID(J6,8,4))))

However, this formula will only ensure that the format is valid. It will not validate the actual date. That is , "Feb 30 2022" will be allowed.

 

[data808]

You can format the cell as Text and anything typed into the cell will be text, and not converted to a date.

You can use data validation with a custom formula (in this example the cell is J6)

=AND(OR(LEFT(J6,3)={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}),MID(J6,4,1)=" ",ISNUMBER(VALUE(MID(J6,5,2))),MID(J6,7,1)=" ",ISNUMBER(VALUE(MID(J6,8,4))))

However, this formula will only ensure that the format is valid. It will not validate the actual date. That is , "Feb 30 2022" will be allowed.

Thanks. I'm having so much issues with this date thing. I just realized I need the cell to stay formatted for dates so that I can autofill 4 more cells to the right in sequence to the 5 day work week. However, this causes some problems since excel takes whatever you type into the cell and puts slashes into the date as you can see in the function bar. That's all fine and dandy but now if I try to do VBA to file save as with those date values as part of the file name, it won't work. Even if I format the cell as a date with dashes, the function bar still auto adjusts it to have slashes. So annoying.

Is there a way for a user to type into one cell with 9/5/2022 then auto populate for the next 4 cells to the right ending on the last cell with 9/9/2022 then do VBA behind a button to save the file as this: "2022_09SEP_05-09.xlsm"?

I can take care of the auto populating and what not. I just need a way to pull in the file name based on those dates with slashes in it.

Thanks.

 

[6StringJazzer]

I can't figure out what you are trying to do with "2022_09SEP_05-09" what does the 09 before SEP mean? Month number?

VBA, where A1 is whatever cell contains the first date of interest. Untested. The key thing is to use the Format function instead of worrying about how the date is displayed on the sheet.

ThisWorkbook.SaveAs FileName:=Format(Range("A1"),"yyyy_mm") & Format(Range("A1"), "MMM_dd-") & Format(Range("E1"), "dd") & ".xlsm"

 

[data808]

I can't figure out what you are trying to do with "2022_09SEP_05-09" what does the 09 before SEP mean? Month number?

VBA, where A1 is whatever cell contains the first date of interest. Untested. The key thing is to use the Format function instead of worrying about how the date is displayed on the sheet.

ThisWorkbook.SaveAs FileName:=Format(Range("A1"),"yyyy_mm") & Format(Range("A1"), "MMM_dd-") & Format(Range("E1"), "dd") & ".xlsm"

Thanks. Yeah the 09 before the SEP is for the month. These files are going to be saved in a folder and I want them to be sorted by the month number rather than alphabetically by the month word. I will try this format function and let you know how it turns out. As I write the code I'll post it if it doesn't work so you can see what's going on. Thanks for the reply and the help.

 

[data808]

I can't figure out what you are trying to do with "2022_09SEP_05-09" what does the 09 before SEP mean? Month number?

VBA, where A1 is whatever cell contains the first date of interest. Untested. The key thing is to use the Format function instead of worrying about how the date is displayed on the sheet.

ThisWorkbook.SaveAs FileName:=Format(Range("A1"),"yyyy_mm") & Format(Range("A1"), "MMM_dd-") & Format(Range("E1"), "dd") & ".xlsm"

Thank you for that wonderful lesson on the format function. That was such an annoying problem but so easily fixed once you have the right tools. Thank you so much for all the help on this. Really appreciate it.

 

[6StringJazzer]

You're welcome!