Help in date and time conversion

[davieddelacruz]

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.

[davesexcel]

Not my strong point, but wouldn't you subtract 8/24?

=C2 -8/24

[davieddelacruz]

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

[63falcondude]

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.

[davieddelacruz]

Sir 63falcondude, tried that but still same value error. Please see: http://prntscr.com/hhubf2. I updated the formula for the column F already.

[davesexcel]

I do not get any errors at all.

TimeLess8.JPG

[davieddelacruz]

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.

[davesexcel]

I am not sure what you mean.

[davieddelacruz]

Sorry sir davesexcel. did you just type the formula in column f? Mine is here: http://prntscr.com/hhunjb

[davesexcel]

The formula is in the cell in Column F

[davieddelacruz]

Yes, it's in the cell in column f sir. See: http://prntscr.com/hhutpb

[davesexcel]

Can't open the file, you should use the forum uploader.

[davieddelacruz]

im attaching the file sir

[davesexcel]

I don't know, I just went in the cell and hit enter and it fixed itself.

[davieddelacruz]

What format did you use for colum f sir?

[davesexcel]

I just used the format painter. Selected C2, clicked the format painter, selected the cells in Column F

[davieddelacruz]

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?

[davesexcel]

Okay C2 was formatted as general, I formatted it as m/dd/yy/h:mm then copied the formats to column F

[davieddelacruz]

I can't make it to work here in my end. I already formatted it based on your recommendation, but still same issue.

[davesexcel]

Darn, hopefully others will have had the same issue.

[davieddelacruz]

Can you share the file that is working on your end sir for me to check on my end?

[Aardigspook]

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):

=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))+TIME(MID(C2,FIND(" ",C2),FIND(":",C2)-FIND(" ",C2)),MID(C2,FIND(":",C2)+1,2),0)-8/24

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:

=IFERROR(C2-8/24,DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))+TIME(MID(C2,FIND(" ",C2),FIND(":",C2)-FIND(" ",C2)),MID(C2,FIND(":",C2)+1,2),0)-8/24)

If all the other dates are wrong too (that is, the month and date are switched), then use this in C2 and drag down:

=IFERROR(DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))+TIME(MID(C2,FIND(" ",C2),FIND(":",C2)-FIND(" ",C2)),MID(C2,FIND(":",C2)+1,2),0)-8/24,(DATE(YEAR(C2),DAY(C2),MONTH(C2))+TIME(HOUR(C2),MINUTE(C2),SECOND(C2)))-8/24)

Hopefully that fixes it for you.


Edit:
Sorry, the formulae above don't take into account am/pm. Use these ones instead:

=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

=IFERROR(C2-8/24,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)

=IFERROR(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,(DATE(YEAR(C2),DAY(C2),MONTH(C2))+TIME(HOUR(C2),MINUTE(C2),SECOND(C2)))-8/24)

[protonLeah]

=(C2- 8/24)*1 formatted: mm/dd/yyyy hh:mm

[xladept]

Try this:

Sub TickleManila(): Dim R As Range, C As Range, F
Set R = Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
For Each C In R: C.FormulaR1C1 = C.FormulaR1C1
Next: End Sub

[davieddelacruz]

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?

[Aardigspook]

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?

You're welcome and thanks for the rep.
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.)

[sandy666]

Most of the world using DMY or like Philippines DMY/MDY
Date format by country

edit:

example via PQ

[samba_ravi]

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