Convert and copy to Date

chaboyski

Board Regular
Joined
Aug 18, 2020
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
Hi,

just wanted to ask if there is a way to automatically convert the time/date and add it correspondingly to the correct date?

currently i am using Philippine time and i wanted to convert it to PST.

for example, PH Time/Date is 4/3 2:30 AM, if i convert it to PST, it would be 4/2 11:30 AM.

would just want to automatically generate the Convert to PST Table either using the Normal Time Zone Table or the Convert PH to PST Table.

Book1.xlsx
ABCDEFGHIJKLMNOP
1Normal Time Zone (PH Time)Converted to PST
2MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
31-Apr2-Apr3-Apr4-Apr5-Apr6-Apr7-Apr1-Apr2-Apr3-Apr4-Apr5-Apr6-Apr7-Apr
4Emp 14/1/2024 20:004/2/2024 20:004/3/2024 20:004/4/2024 20:004/5/2024 20:00OFFOFF4/1/2024 5:004/2/2024 5:004/3/2024 5:004/4/2024 5:004/5/2024 5:00OFFOFF
5Emp 2OFFOFF4/3/2024 2:304/4/2024 2:304/5/2024 2:304/6/2024 2:304/7/2024 1:30OFF4/2/2024 11:304/3/2024 11:304/4/2024 11:304/5/2024 11:304/6/2024 10:30OFF
6Emp 3OFF4/2/2024 2:304/3/2024 2:304/4/2024 2:304/5/2024 2:304/6/2024 2:30OFF4/1/2024 11:304/2/2024 11:304/3/2024 11:304/4/2024 11:304/5/2024 11:30OFFOFF
7
8Convert PH to PST
9Emp 14/1/2024 5:004/2/2024 5:004/3/2024 5:004/4/2024 5:004/5/2024 5:00OFFOFF
10Emp 2OFFOFF4/2/2024 11:304/3/2024 11:304/4/2024 11:304/5/2024 11:304/6/2024 10:30
11Emp 3OFF4/1/2024 11:304/2/2024 11:304/3/2024 11:304/4/2024 11:304/5/2024 11:30OFF
Sheet2
Cell Formulas
RangeFormula
B9:H11B9=IFERROR(B4-"15:00","OFF")
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You are 3 hours ahead of PT, yes? So is this not just a matter of adding 3 hours to the numeric value of the date?
So 4/1/2024 5:00 is 45383.208333333
3 hours is 0.1250 of a day, thus PT is that number minus .125

Or do I have it backwards and you are 3 hours behind? Also, I think there is PT and PST so does that matter?
 
Upvote 0
it would be the dates that i'm confused. since if PT crossover midnight, the date is advanced vs PST
 
Upvote 0
Dates are stored as floating Double data type. At the point I just asked moments ago, Now() was 45376.7288194444
The day portion is left of the decimal. Tomorrow is 45377.
The representation (like 03/25/2024, with or without time), is just a format of that number. So I don't see how yesterday vs tomorrow should be a problem - unless your date data is a string and not a date. If you don't know, you can choose a cell with a date and check its format when you specify Number (or perhaps General). If you see a number like I posted, that date value is date data type.
 
Upvote 1
Solution
got it. thanks!

im just thinking like if i can use index/match to get the precise date. but i guess it cannot be done. there might be a way but i havent figured it out yet. thank you!
 
Upvote 0
Wish I could help with a formula for that, but I don't even know what Index does.
BTW, I forgot to mention that the numbers to the right of the decimal represent the portion of a day, so .50 would mean you're at noon.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top