So what about:
=TEXT(43592,"mm")&"/"&TEXT(43592,"dd")&"/"&TEXT(43592,"yy")
Or reference a cell where you have put your date.
Unfortunately I don't think it is possible - see the this link
Quote:
Problem: The value in the date_text argument is not in sync with the system’s date and time settings*
If your system date and time settings follow the mm/dd/yyyy format, then a formula such as =DATEVALUE(“22/6/2000”) will result in a #VALUE! error. But the same formula will display the correct value when the system's date and time is set to dd/mm/yyyy format.
Solution: Make sure that your system’s date and time settings (both Short time and Long time) matches the date format in the date_text argument.
I think the only solution is to make sure that your date is in the valid (i.e. recognised by the system) date format before any other function (like DATEVALUE) is applied.
It will be easy to achieve if your function is simply =text(5/7/19,[Windows date format]). You can reference your cell field (e.g. =[@Date]) and format a given cell using an "universal" date format (the ones that start with * sign).
However, if you are combining =text(5/7/19,[Windows date format]) with some other formula (e.g. when you want to display a full sentence like A very important event happened on 5/7/19) then, unfortunately, cell format won't save you here and we're out of solutions. In such case the end user would need to manually modify the date cell in order to make it recognisable.
I’m looking for some help with a project that is WAY beyond my current skillset. I’m sure there is a way to do all of this in Excel, but I don’t know where to start or what to search for for help. I’m seriously considering hiring a freelancer for this project, but again, I think I need a little advice on where to start so I know what to ask a freelancer for.
Here’s what I’m trying to do (keeping it somewhat vague):
Organize and compare costs from multiple manufacturers in various regions, for a range of quantities. Manufacturers end up providing costs in different formats and configurations. Example:
* Unit cost * Plant + Unit cost * Cost per 1,000 * Detailed cost breakdown (Base + component 1 + component 2 + component 3)
-
Need a way to standardize costs
-
Need a way to visualize data for comparison
-
sheet that picks up standardized costs from elsewhere to show data side-by-side; show the difference between manufacturers within a certain region, and between different regions.
For me, this all seems like it would be a huge undertaking, but I’m not sure how difficult this project would be for someone who is more of an expert in excel. Any help or advice would be greatly appreciated