I’m dealing with dates located in column A formatted as mm/dd/yyyy. My goal is to convert these into yyyy;mm;dd for usage in the DATE function dynamically.
Currently, I have a formula that uses hardcoded dates. Here it is:
I need to adjust this to pull the date from cell A2 (which is 12/30/2011) instead of using the fixed “2011;12;30”. Additionally, I want to automate the addition of one day so that it captures the next date as well (2011;12;31). Any suggestions on how to accomplish this?
Here’s what worked for me - skip DATEVALUE and use Google Sheets’ built-in date parsing. Since Sheets already recognizes mm/dd/yyyy format, you can reference A2 directly in comparisons:
No DATEVALUE needed. Sheets treats A2 as a date automatically when you do math with it. Adding 1 still gets you the next day. This runs faster on big datasets since there’s fewer function calls. Just make sure your date column stays formatted consistently.
I’ve hit this same issue with date conversions in Google Sheets. Use DATEVALUE to convert your text dates into actual date values for calculations. Replace those hardcoded DATE functions with DATEVALUE(A2) and DATEVALUE(A2)+1. This converts your mm/dd/yyyy format straight into a date serial number that Sheets understands.
No more manual parsing - DATEVALUE handles the conversion and adding 1 gets you the next day. Just make sure your column A dates stay consistently formatted as mm/dd/yyyy text.
you can also use the TEXT function to pull out pieces - TEXT(A2,“yyyy”) for year, TEXT(A2,“mm”) for month, TEXT(A2,“dd”) for day. then rebuild it with DATE(TEXT(A2,“yyyy”),TEXT(A2,“mm”),TEXT(A2,“dd”)). it’s wordier but gives you complete control when DATEVALUE gets wonky.