Need to convert time and date together from one timezone to another

[shadowknight]

First column the date and time are together and i need to go back 10 hours which would be 30th september 2019 14:00 PM ,

Can you please provide the formula

01/10/2019 00:00:29
01/10/2019 00:01:16
01/10/2019 00:01:51

[FDibbins]

Try this...
=A2-(10/24)

[shadowknight]

Hi i need to subtract 6 hours from the below , i have tried the formula A1-6/24 , it doesnt work . It is a 12 hour AM/PM time format

6/25/2020 2:59:56 PM

[AliGW]

You need brackets:

=A1-(6/24)

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[shadowknight]

Hello AliGW

It does work and puts the word "VALUE" in cell and says the value used in the formula is of the wrong data type.

[AliGW]

OK - attach a workbook.

[shadowknight]

I am unable to attach the file. Can you copy the text that i have provided into singles cell without any spaces and provide me the workbook with the formula

6/24/2020 8:24:04 PM
6/25/2020 2:39:56 PM
6/27/2020 3:28:51 PM
6/28/2020 3:32:37 PM
6/25/2020 4:15:19 PM
6/25/2020 4:07:18 PM

[AliGW]

No, because that won't get to the bottom of the problem, sorry.

You need to provide a WB that shows the issue.

What is stopping you?

The dates you have just posted are in US format - I suspect there is a settings issue, but I can't be sure without seeing the WB.

[shadowknight]

How do i attach it ? i am unable to see the option.

[AliGW]

Start a post, then click Go Advanced bottom right - scroll down to Manage Attachments.

[shadowknight]

Hi

I have uploaded the attachment here

[AliGW]

As I suspected, the entries are just text, not date values. Where have they come from? How have you imported them? They Are in US date format, so you are going to need to convert them to dd/mm/yyyy before you can work with them.

https://kickthatcomputer.wordpress.c...rmat-in-excel/

[AliGW]

You could also try this formula in B2:

=IF(ISTEXT(A2),--SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(A2,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9)&REPLACE(SUBSTITUTE(LEFT(TEXT(A2,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9,""))," ","/")+IF(ISTEXT(A2),--MID(A2,FIND(" ",A2)+1,99),A2),A2)

This will convert your text into date format.

[shadowknight]

Hi AliGW

Thanks i have tried the steps provided in the link but it doesnt work , The entry stays as 6/24/2020 8:24:04 PM and doesnt get converted into 24/6/2020 8:24:04 PM

I have also tried your formula , it converts text into date format but it doesnt subtract 6 hours from the time , it has added 18 hours to the time


6/24/2020 8:24:04 PM has become 25/06/2020 14:39

[AliGW]

From the workbook attached to my last post:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Timezone
2	6/24/2020 8:24:04 PM	24/06/2020 20:24	25/06/2020 02:24
3	6/25/2020 2:39:56 PM	25/06/2020 14:39	25/06/2020 20:39
4	6/27/2020 3:28:51 PM	27/06/2020 15:28	27/06/2020 21:28
5	6/28/2020 3:32:37 PM	28/06/2020 15:32	28/06/2020 21:32
6	6/25/2020 4:15:19 PM	25/06/2020 16:15	25/06/2020 22:15
7	6/25/2020 4:07:18 PM	25/06/2020 16:07	25/06/2020 22:07

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B	C
2	=IF(ISTEXT(A2),--SUBSTITUTE(TRIM(MID(SUBSTITUTE(LEFT(TEXT(A2,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9)&REPLACE(SUBSTITUTE(LEFT(TEXT(A2,"dd/mm/yyyy"),10),"/",REPT(" ",9)),9,9,""))," ","/")+IF(ISTEXT(A2),--MID(A2,FIND(" ",A2)+1,99),A2),A2)	=B2+(6/24)

Sheet: Sheet1

[shadowknight]

Hi Ali

Never mind , i have seen the file you provided and have used the formula from it and it works now

Thanks for your help

[AliGW]

No worries.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.