Hi guys,
Please help me. I'm trying to convert the date to PST timezone from Manila timezone. However, due to the difference in the date and time format, some returns a #value error. Can someone check this sample file for me? I ried to seperate the time and date then change format, but still no avail.
Not my strong point, but wouldn't you subtract 8/24?
Please Login or Register to view this content.
Hi sir,
Even if I use that, the result showws a "Value" error. I think there's something wrong with the format that's why it shows a value error
The dates in columns C and D (specifically in rows 2, 6, 8, 10, and 14) are being seen as text.
Highlight column C > Data > Text to Columns > Next > Next > Date: MDY > Finish
Do the same for column D.
Then your formulas should work as expected.
Sir 63falcondude, tried that but still same value error. Please see: http://prntscr.com/hhubf2. I updated the formula for the column F already.
I do not get any errors at all.
TimeLess8.JPG
That's weird. Data>text to columns> delimited>next>no need to select anything in the delimiters (except the checked tab)>next>select date to MDY>finish. That's what I'm doing sir.
I am not sure what you mean.
Sorry sir davesexcel. did you just type the formula in column f? Mine is here: http://prntscr.com/hhunjb
The formula is in the cell in Column F
Yes, it's in the cell in column f sir. See: http://prntscr.com/hhutpb
Can't open the file, you should use the forum uploader.
im attaching the file sir
I don't know, I just went in the cell and hit enter and it fixed itself.
What format did you use for colum f sir?
I just used the format painter. Selected C2, clicked the format painter, selected the cells in Column F
I tried that, but to no avail. Just to be sure, I redownloaded the file from its source to make sure that I have a fresh data. And restarted everything from the scratch. Here's the new file. Hope you can check it. If possible, can you send the file that is working on your end for me to check?
Okay C2 was formatted as general, I formatted it as m/dd/yy/h:mm then copied the formats to column F
I can't make it to work here in my end.I already formatted it based on your recommendation, but still same issue.
Darn, hopefully others will have had the same issue.
Can you share the file that is working on your end sir for me to check on my end?
I think your problem is with your regional settings. If your computer is setup to use the international date format of d/m/y then Excel doesn't understand 11/16/2017 as a date (because there's no 16th month). The other dates are probably being translated wrongly as well - try formatting column C as d mmm yy to see if 11/10/2017 shows as 11 Oct 17 (international) or as 10 Nov 17 (USA).
To fix the cells with values , you can use this (bit long, sorry):
Formula:
Please Login or Register to view this content.
That assumes that the date/time in the 'not working' cells is always in the format mm/dd/yyyy h:mm AM - which it looks to be.
This formula will not work on the other cells. What you do with them depends on whether they're correct (month/day or day/month) or not.
If the other dates are correct (day and month correct, not switched), then use this in C2 and drag down:
Formula:Please Login or Register to view this content.
If all the other dates are wrong too (that is, the month and date are switched), then use this in C2 and drag down:
Formula:Please Login or Register to view this content.
Hopefully that fixes it for you.
Edit:
Sorry, the formulae above don't take into account am/pm. Use these ones instead:
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Last edited by Aardigspook; 12-01-2017 at 06:08 PM. Reason: Add correction for PM times
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
=(C2- 8/24)*1 formatted: mm/dd/yyyy hh:mm
Ben Van Johnson
Try this:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Wow! this has done it sir @aardigspook!
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))+TIME(IF(RIGHT(C2,2)="AM",MID(C2,FIND(" ",C2),FIND(":",C2)-FIND(" ",C2)),MID(C2,FIND(" ",C2),FIND(":",C2)-FIND(" ",C2))+12),MID(C2,FIND(":",C2)+1,2),0)-8/24
Thanks so much. Now, I'm curious about the regional settings thing since mine is using it based on the formula you provided as well. What's the reason for that? And how can I change it to USA settings?
Re: Help in date and time conversion
Most of the world using DMY or like Philippines DMY/MDY
Date format by country
edit:
example via PQ
Last edited by sandy666; 12-01-2017 at 07:53 PM.
or try
=IF(ISNUMBER(C2+0),DATE(YEAR(C2),DAY(C2),MONTH(C2))+MOD(C2,1),DATE(MID(C2,7,4),MID(C2,1,2),MID(C2,4,2))+MID(C2,12,10)+0)- 8/24
and copy towards down
Last edited by samba_ravi; 12-02-2017 at 12:59 AM.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
You're welcome and thanks for the rep.Originally Posted by davieddelacruz
If you're using Windows, the easiest way to check/change your regional settings is something like this (the instructions below are correct for Windows 7 - they will probably vary a bit for 8/8.1/10 but shouldn't be too different):
1. Open the Start menu;
2. In the search box, type 'Region';
3. One of the results found (hopefully the top one) will be 'Region and Language' - select it;
4. In the window which opens, you have two options:
a. change the main 'Format' to 'English (United States)' which will change all your settings to USA standards;
b. leave the main format as you have now and click 'Additional Settings' to change the 'Short date' format from some variation of d/m/yy to whatever format of m/d/yy you'd prefer (mm/dd/yy or mm-d-yy or whatever).
(You'll see that there are options for changing your preferred time format, currency, etc as well as date.)
Hope that helps.
(If you're using something other than Windows, then I'm sorry but I can't help with that.)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks