need to get right date
Greeting to all
as mentioned i need to get from only date like (14/12/2023) from (14/12/2023 21:20:34)
i used =DATE(MID(J4,7,4), MID(J4,4,2), MID(J4,1,2)) but its not working in all cases like (6/1/2024 21:45) or (1/1/2024 20:18:59)
need something to work with all cases
Try:
=INT(J4)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
error #VALUE
No error if the date is real - see attached.
Attach a workbook showing the error.
Or try:
=--LEFT(A1,10)
the problem seems to be in the report formatting 14/12/2023 21:20:34
it sees 14 as year and 2023 as a day
what should i do ?
Provide me with a workbook showing this. I cannot troubleshoot thin air.
Here's what it looks like for me:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Sheet1
--LEFT(A1,10) isn't working in all cases either. eg. not in case 1/1/2024 21:45. See Post #1
Please try:and format cell as a date.Formula:
Please Login or Register to view this content.
Did you check the format of the cell?it sees 14 as year and 2023 as a day
what should i do ?
yes i attached file also
That's a CSV file, not an Excel workbook. The dates present as real dates, not text, so =INT(A2) works:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
created_at
Sheet: Transactions_Export_21482__07_0
after i saved the file to .xlsx INT(A2) still not working
im not sure if i missing something here
I saved as XLSX and INT(A2) worked as expected
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
If you open the .xlsx file I attached, you will see the INT function working. When you then save that to .csv, the formula results are changed into date values.Originally Posted by manshyworld
after i saved the file to .xlsx INT(A2) still not working
im not sure if i missing something here
You are either missing something or doing something wrong, because it works fine with your data.
in my sheet the formation is 6/1/2024 21:45:16 while in your sheet 2024/1/06 21:45
can you see the difference (Arrange - missing seconds)
what did you do ?
i meant you
I don't understand - there is no formation like that in my workbook.in my sheet the formation is 6/1/2024 21:45:16 while in your sheet 2024/1/06 21:45
But the second is just yyyy/m/dd hh:mm - it's the same data.
Look at the attachment: columns C and E are the same dates, just formatted differently.
Last edited by AliGW; 01-07-2024 at 01:33 PM.
In my file A2>>B2
06/01/2024 21:45:16 >>>06/01/2024
A2 format is dd/mm/yyyy hh:mm
B2 format is dd/mm/yyyy
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks