Convert and copy to Date

[chaboyski]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Normal Time Zone (PH Time)								Converted to PST
2		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
3		1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr		1-Apr	2-Apr	3-Apr	4-Apr	5-Apr	6-Apr	7-Apr
4	Emp 1	4/1/2024 20:00	4/2/2024 20:00	4/3/2024 20:00	4/4/2024 20:00	4/5/2024 20:00	OFF	OFF		4/1/2024 5:00	4/2/2024 5:00	4/3/2024 5:00	4/4/2024 5:00	4/5/2024 5:00	OFF	OFF
5	Emp 2	OFF	OFF	4/3/2024 2:30	4/4/2024 2:30	4/5/2024 2:30	4/6/2024 2:30	4/7/2024 1:30		OFF	4/2/2024 11:30	4/3/2024 11:30	4/4/2024 11:30	4/5/2024 11:30	4/6/2024 10:30	OFF
6	Emp 3	OFF	4/2/2024 2:30	4/3/2024 2:30	4/4/2024 2:30	4/5/2024 2:30	4/6/2024 2:30	OFF		4/1/2024 11:30	4/2/2024 11:30	4/3/2024 11:30	4/4/2024 11:30	4/5/2024 11:30	OFF	OFF
7
8		Convert PH to PST
9	Emp 1	4/1/2024 5:00	4/2/2024 5:00	4/3/2024 5:00	4/4/2024 5:00	4/5/2024 5:00	OFF	OFF
10	Emp 2	OFF	OFF	4/2/2024 11:30	4/3/2024 11:30	4/4/2024 11:30	4/5/2024 11:30	4/6/2024 10:30
11	Emp 3	OFF	4/1/2024 11:30	4/2/2024 11:30	4/3/2024 11:30	4/4/2024 11:30	4/5/2024 11:30	OFF
Sheet2

Cell Formulas
Range	Formula
B9:H11	B9	=IFERROR(B4-"15:00","OFF")

 

[Micron]

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?

 

[chaboyski]

it would be the dates that i'm confused. since if PT crossover midnight, the date is advanced vs PST

 

[Micron]

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.

 

[chaboyski]

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!

 

[Micron]

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.