Convert Column of DateTime to Date

[tangmere.milli]

Hi all,

I have the following MS Query which is displaying the 'DespatchDate' column as 'DateTime';

Please Login or Register  to view this content.

I need this to convert to 'Date'. I don't want it to convert to the 'nearest' date to the time. Literally just need the time removed from the column so I can then do lookups to this column in Excel.

Anyone have a clue how to do this easily?

Many thanks in advance,

TM

[davsth]

If the date is returned as a date time to excel just change your lookup to lookup int(field containing date time)


int(date time)=date and use the rest of your formula as you intended

[tangmere.milli]

Hi,

Many thanks for thw quick reply. I have this formula doing multiple lookups;

Please Login or Register  to view this content.

The highlighted in red part is the lookup for the date from the query.

How would I adjust this without messing up the entire formula?

[davsth]

duplicate post

[davsth]

Probably the first of the below is what you want. An attachment makes things easier, but this should avoid the array formula

sumproduct(('Actual Sales (Update Dail (Ent)'!$A$2:$A$1547='Actual (Ent)'!$A$2)*('Actual Sales (int(Update Dail (Ent)'!$D$2:$D$1547)='Actual (Ent)'!$A24)*('Actual Sales (Update Dail (Ent)'!$C$2:$C$1547='Actual (Ent)'!B23)*('Actual Sales (Update Dail (Ent)'!$B$2:$B$1547='Actual (Ent)'!D23)*('Actual Sales (Update Dail (Ent)'!$E$2:$E$1547))

sumproduct(('Actual Sales (Update Dail (Ent)'!$A$2:$A$1547='Actual (Ent)'!$A$2)*('Actual Sales (Update Dail (Ent)'!$D$2:$D$1547=int('Actual (Ent)'!$A24))*('Actual Sales (Update Dail (Ent)'!$C$2:$C$1547='Actual (Ent)'!B23)*('Actual Sales (Update Dail (Ent)'!$B$2:$B$1547='Actual (Ent)'!D23)*('Actual Sales (Update Dail (Ent)'!$E$2:$E$1547))