IF function to return date content of another cell

[lesg46]

I have searched many posts but can’t seem to find anything similar, so I am
having to ask for assistance please. I’m sure it will be a piece of cake for
someone, given the very in-depth issues I’ve been searching through!

I have many workbooks that I may need to apply this to, each with many
thousands of lines.

I am importing this data from an Access database (that I can’t modify as
it’s not mine).

I import each sheet as follows:

B C D E
1 Date Item Qty Identity
2 11/14/2007 Apple 1 Customer A
3 Banana 1 Customer A
4 11/14/2007 Apple 1 Customer B
5 11/14/2007 Apple 1 Customer C
6 Orange 2 Customer C
7 11/14/2007 Orange 1 Customer D

But where the same customer has bought more than one item on a day, the date
is missing for the second item.

I need to create a pivot table based on all this data, however as I have
blank date cells, obviously the table doesn’t complete correctly.

I had thought the easiest way to get round this would be to insert a column
before the Date column and insert a formula. I have tried =IF(B3>0,B3,B2).
i.e. if the cell to the right has a date in it, insert that date, otherwise
insert the date from the row before. (There will never be an instance where
the date for the first item purchased is blank.) However I’m obviously
missing something as this doesn’t work.

I’ve also tried =IF(B3>0,B3,IF(B3<=0,B2)). But that doesn’t work either.

I am very grateful for any help.
Les

 

[FSt1]

hi
you are downloading the access report. what you need to do is download the
access query that the report uses. you problem will go away.

regards
FSt1

 

[Gary Brown]

Since treating the 'dates' as values doesn't seem to be working, that
suggests that, although the items LOOK like dates, they've been imported from
Access as Strings (which have a -0- value).
Try something like...
=IF(LEN(TRIM(B3))=0,A2,TRIM(B3))

 

[T. Valko]

The easiest way....

Select the range of dates in column B
Hit function key F5>Special>Blanks>OK
This will select all the empty cells within the range
Type an = sign then hit the up arrow directional key then hold down the CTRL
key and hit ENTER.

If you want to use a formula in a helper column...

With dates in column B starting at B1...

Enter this formula in C1: =B1

Enter this formula in C2 and copy down as needed:

=IF(B2="",C1,B2)

 

[lesg46]

Hi Gary,
Thanks for your help. It does indeed work, but actually only after it
didn't work initially and my discovery of why not! The blank cells weren't
actually blank - they contained a space. Which now explains why my version
didn't work either.
Thanks very much though, I've added my new knowledge to my ever increasing
list of useful Excel things.
Best regards
Lesley

 

[lesg46]

Hi Biff,
It was as a result of your first suggestion that I discovered why Gary's
solution didn't work. I couldn't select the Blanks in the date column.
Because there weren't any! So once I'd corrected this, it all worked
beautifully.

Thanks very much.
Best Regards
Lesley

 

[lesg46]

Hi,
Sorry, I should have been a bit more clear. The report has already been
exported from an Access database, so I receive it as a txt file. I have no
way of changing what comes out of the database.

Many thanks for taking the time to reply though.

Best regards
Lesley

 

[T. Valko]

You're welcome. Thanks for the feedback!