How to Use this format in my formula. dd/mm/yyyy hh:mm

[Pooja Patel]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name - Column A[/TD]
[TD]Date - Column B[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]01/17/2018 12:09:00 AM[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]01/17/2018 12:08:00 AM[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]01/17/2018 12:07:00 AM[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]01/17/2018 12:06:00 AM[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]01/17/2018 12:09:00 AM[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]01/17/2018 12:08:00 AM[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]01/17/2018 12:07:00 AM[/TD]
[/TR]
[TR]
[TD]Xyz[/TD]
[TD]01/17/2018 12:06:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:09:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:08:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:07:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:06:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:05:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:04:00 AM[/TD]
[/TR]
[TR]
[TD]ASAF[/TD]
[TD]01/17/2018 12:03:00 AM[/TD]
[/TR]
</tbody>[/TABLE]


Dear Friends,

Need Urgent Help. I need help for create automatic formula for "DATE" in column B.

In Column A, There are equal type of name coming.

is there any method or formula for automatic date change when name change in Column A.

Thanks In Advance.

Regards,
Excel Beginner User

 

[Pooja Patel]

Dear jtakw,

Thanks for your kind help.

I have checked the cell format. It is show like that mm/dd/yyyy hh:mm:ss AM/PM

Your formula work when name changed different in Column A, When Name coming same text then its not subtract from Column B.


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]Name - Column A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="width: 256"]Date - Column B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]Xyz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]abc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]Xyz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]ASAF[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 116"]
<tbody>[TR]
[TD="width: 116"]Xyz[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 256"]01/17/2018 12:09:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Works for me:

Excel 2010
	A	B	C	D
1	Name - Column A	Date - Column B
2	abc	01/17/2018 12:09:00 AM		TRUE
3	Xyz	01/17/2018 12:09:00 AM
4	abc	01/17/2018 12:09:00 AM
5	abc	01/17/2018 12:08:00 AM
6	abc	01/17/2018 12:07:00 AM
7	abc	01/17/2018 12:06:00 AM
8	Xyz	01/17/2018 12:09:00 AM
9	ASAF	01/17/2018 12:09:00 AM
10	Xyz	01/17/2018 12:09:00 AM
Sheet8

Cell Formulas
Range	Formula
D2	=ISNUMBER(B2)
B3	=IF(A3=A2,B2-"0:01",B$2)

Besides checking that Column B formatting is mm/dd/yyyy hh:mm:ss AM/PM, you need to input your date/time correctly, in an unused cell (in my sample D2) put =ISNUMBER(B2), if it doesn't return TRUE, you have entered B2 as TEXT, and the formula will NOT calculate.

 

[Pooja Patel]

Dear jtakw ,

Thanks for your great help.

Finally problem is solved.

 

[jtakw]

Dear jtakw ,

Thanks for your great help.

Finally problem is solved.

You're welcome, welcome to the forum, glad it worked out for you.