Simple copy pest data with logic

**arindamsenaxa** · 03-02-2015, 07:03 AM

Hi
Please help me to create the formula
please go through the attach file
in Sheet1 I have some data
in Sheet2 if I entered any ACC no which is there in A1 of Sheet1 the Last Reopen Date should automatically pick from Sheet1
is this possible with any formula?

Regards
Arindam

**Fotis1991** · 03-02-2015, 07:09 AM

Formula:

Please Login or Register  to view this content.

**arindamsenaxa** · 03-02-2015, 07:22 AM

Hi
Thanks for your response .

request you to please attach the same excel file by putting the formula.
becoz y r formula is not working in my sheet

Regards
Arindam

**arindamsenaxa** · 03-05-2015, 01:45 AM

Hi Fotis1991
your formula is working fine with me except one single problem.
with this formula I am copying one date.the date is in PST format I am trying to convert in IST by adding 12:30:00
but it is not giving any value.is there any other solution.
please check the Sheet "Assign window" in "D" i have entered your formula
and in D i want the IST time. but it is not giving any value

**samba_ravi** · 03-05-2015, 02:30 AM

No such sheet found in the attached file
Try this
=A1+TIME(12,30,00)
CHANGE A1 according to your need

**arindamsenaxa** · 03-05-2015, 05:54 AM

Hay sorry for the inconvenience
now I have attached the correct file .in this i have tried your formula ,still it is not working.
Please help me out

**samba_ravi** · 03-05-2015, 06:17 AM

=DATEVALUE(SUBSTITUTE(MID(D3,6,6),"-"," ")&", "&LEFT(D3,4))+RIGHT(D3,5)+E3
or
=DATEVALUE(MID(D3,6,3)&" "&MID(D3,10,2)&", "&LEFT(D3,4))+RIGHT(D3,5)+E3

**arindamsenaxa** · 03-12-2015, 02:29 AM

Hi nflsales
sorry for late response.thanks for your response,
I have tried your formula but I think still it is not working
please check the attach file..i have applied your formula in "E"

Please help me and if possible can you please explain me the problem and how the the formula is working, then i can have a understanding for the next time
Regards
Arindam