Move Date to fill in time/date area

[Sailman]

Goal is to move the Date from column B to Column A on rows that have a time and event and details .

the row with the date currently is blank except for column B.

this is for 90 days worth of data on a regular basis for 4 different spreadsheets.

 

[Toadstool]

Hi Sailman,

A couple of questions:

1. Are you sure the times only have a time and not the date and time? Click on that 4:30:12 cell and see what appears in the formula bar; is it just the time or something like 1/1/2021 4:30:12 AM ?
2. When you say "rows that have a time and event and details" do you mean both are non-zero? e.g. what about "Denied" and Details of zero, should they also have the date?

 

[Toadstool]

Pending your response here's a version which puts the year if columns C and D aren't blank:

Sailman.xlsx
	A	B	C	D
4	Date	Date/Time	Event	Details
5		02-Jan-21
6	02-Jan-21	4:30:12	Okay	112.3
7	02-Jan-21	4:30:12	Denied	0
8	02-Jan-21	4:30:12	Okay	112.3
9	02-Jan-21	4:30:12	Denied	112.3
10	02-Jan-21	4:30:12	Okay	112.3
11	02-Jan-21	4:30:12	Denied	112.3
12	02-Jan-21	4:30:12	Okay	112.3
13	02-Jan-21	4:30:12	Denied	112.3
14		03-Jan-21
15	03-Jan-21	4:30:12	Okay	112.3
16	03-Jan-21	4:30:12	Denied	112.3
17	03-Jan-21	4:30:12	Okay	112.3
18	03-Jan-21	4:30:12	Denied	112.3
19
Sheet1

Cell Formulas
Range	Formula
A6:A19	A6	=IF(C6&D6="","",INDEX($B$5:$B$19,AGGREGATE(14,6,ROW($B$5:$B$19)-ROW($B$4)/(($B$5:$B$19>1)*($C$5:$C$19="")*(ROW($D$5:$D$19)<ROW())),1)))

 

[Sailman]

Thank you for this help, yes the C and D columns are blank where there are dates in Column B.
the time is just the time.
I tried your formula, it may take me a few times to figure out all the ins and outs but I think I understand the general direction you are going with the formula.
Index seems to be good idea, and maybe create a value on with the date value.

 

[Toadstool]

Here, let me explain.

=IF(C6&D6="",""
If Event and Details are blank for this row then there's no need for the date.

INDEX($B$5:$B$19
I want the date so I need to retrieve from column B

AGGREGATE(14,6,ROW($B$5:$B$19)-ROW($B$4)/
I want to retrieve the latest ate so I use the LARGE function of AGGREGATE and check every row. Next I will divide each row number by the results of three logical comparisons multiplied, so only if all three conditions are true will I return a logical one which divided into the row gives me the row number meeting the criteria. i.e. the row with the date.

(($B$5:$B$19>1)*($C$5:$C$19="")*(ROW($D$5:$D$19)<ROW())),1)))

Here's the three criteria:​

​

(($B$5:$B$19>1)*​

The B column must be greater than 1, so it won't be text or time (as time is held as a fraction of 1).​

​

($C$5:$C$19="")*​

The C column must be blank, so I know it's not an Event/Detail row.​

​

(ROW($D$5:$D$19)<ROW())​

The Date it finds must be in a row less than the current row.​

So INDEX retrieve the contents of B where I find the highest row with a date before the row I'm on.