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

[Pooja Patel]

Name - Column A	Date - Column B
abc	01/17/2018 12:09:00 AM
abc	01/17/2018 12:08:00 AM
abc	01/17/2018 12:07:00 AM
abc	01/17/2018 12:06:00 AM
Xyz	01/17/2018 12:09:00 AM
Xyz	01/17/2018 12:08:00 AM
Xyz	01/17/2018 12:07:00 AM
Xyz	01/17/2018 12:06:00 AM
ASAF	01/17/2018 12:09:00 AM
ASAF	01/17/2018 12:08:00 AM
ASAF	01/17/2018 12:07:00 AM
ASAF	01/17/2018 12:06:00 AM
ASAF	01/17/2018 12:05:00 AM
ASAF	01/17/2018 12:04:00 AM
ASAF	01/17/2018 12:03:00 AM

<tbody>
</tbody>

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

 

[Gerald Higgins]

Please describe what the results should be.

 

[Special-K99]

@ Pooja.

Pls clear out your message inbox.
I cannot reply to your PM due to this.


Pooja Patel has exceeded their stored private messages quota and cannot accept further messages until they clear some space.


However my reply was:

Gerald Higgins replied asking for your expected output.
This looks like you want to know when data is changed. This would require a VBA solution but that's not my area of expertise.

 

[Pooja Patel]

Create a formula on below boxes.

Name - Column A	Date - Column B
abc	01/17/2018 12:09:00 AM - Write this date manual. It Called Name - B2
abc	01/17/2018 12:08:00 AM - this will come automatic according to above date.
abc	01/17/2018 12:07:00 AM - this will come automatic according to above date.
abc	01/17/2018 12:06:00 AM
Xyz	01/17/2018 12:09:00 AM - if Column A "XYZ" name change from "ABC" then Again write Same Date from B2
Xyz	01/17/2018 12:08:00 AM
Xyz	01/17/2018 12:07:00 AM
Xyz	01/17/2018 12:06:00 AM
ASAF	01/17/2018 12:09:00 AM - if Column A "ASAF" name change from "XYZ" then Again write Same Date from B2
ASAF	01/17/2018 12:08:00 AM
ASAF	01/17/2018 12:07:00 AM
ASAF	01/17/2018 12:06:00 AM
ASAF	01/17/2018 12:05:00 AM
ASAF	01/17/2018 12:04:00 AM
ASAF	01/17/2018 12:03:00 AM

<tbody>
</tbody>

 

[Gerald Higgins]

OK, so it LOOKS LIKE the requirement is as follows -

For each change in name in column A, make the date and time be the same as the first entry in the list.
For each entry in column A which is the same as the preceding entry, make the time be 1 minute earlier than the previous entry.

Is that correct ?

If yes, this should be fairly simple.

 

[Pooja Patel]

Yes, It is correct. Sometime i will stuck in simple formula also.

 

[Pooja Patel]

Hello friends,

Can i get any solution or formula for above query.

Thanks in advance.

 

[jtakw]

Hi,

Is this what you need?

Book1
	A	B
1	Name - Column A	Date - Column B
2	abc	01/17/2018 12:09:00 AM
3	abc	01/17/2018 12:08:00 AM
4	abc	01/17/2018 12:07:00 AM
5	abc	01/17/2018 12:06:00 AM
6	Xyz	01/17/2018 12:09:00 AM
7	Xyz	01/17/2018 12:08:00 AM
8	Xyz	01/17/2018 12:07:00 AM
9	Xyz	01/17/2018 12:06:00 AM
10	ASAF	01/17/2018 12:09:00 AM
11	ASAF	01/17/2018 12:08:00 AM
12	ASAF	01/17/2018 12:07:00 AM
13	ASAF	01/17/2018 12:06:00 AM
14	ASAF	01/17/2018 12:05:00 AM
15	ASAF	01/17/2018 12:04:00 AM
16	ASAF	01/17/2018 12:03:00 AM
Sheet15

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

B2 manually entered, B3 formula copied down.

 

[Pooja Patel]

Thanks for the reply.

I try above formula but show me error like #VALUE ! in B3 cell.


I use time formula as below but its only work with time mention. When I apply with date then its not work.

=IF(A3=A2,B2-TIME(0,1,0),B$2)


Can you suggest some other formula?

 

[jtakw]

Thanks for the reply.

I try above formula but show me error like #VALUE ! in B3 cell.


I use time formula as below but its only work with time mention. When I apply with date then its not work.

=IF(A3=A2,B2-TIME(0,1,0),B$2)


Can you suggest some other formula?

As you can see, my formula works in my sample, you should check your cell formatting for Column B, it should be custom formatted:

mm/dd/yyyy hh:mm:ss AM/PM