Excel 2007 - Difference in Time

[Kesh]

I need to select records from an Excel worksheet which has several columns but with a certain criteria.

There is a column called "Call Scheduled for" which contains data and time. So if I run a macro every morning at 8:00 a.m. the records selected from the worksheet will be those which are stale for 72 hours or more.

Can anybody please help me write a formula in excel? Thank you in advance

Kesh

 

[Trevor G]

Wouldn't a Filter do this for you?

 

[Kesh]

Thank you Trevor. A filter will not tell you if it is 72 hours stale because the date and time is in 1 cell in the format DD/MM/YYYY HH:MM:SS. In my case it is required to compare the value for "Call Scheduled for" with Today's date and time and find the diffrence on a daily basis. So everyday a program will run at 8:00 a.m. select the records stale for 72 hours and more generate a worksheet and send it by e-mail as attachment. All this without any manual intervention

A VBA code will be more appropriate.

Kesh

 

[Trevor G]

Can you attach an example of your workbook, you can always strip out any sensitive data. When you refer to stale data does this refer to data that is older by 72 hours? And what other criteria is involved? How is the spreadsheet to be attached as a copy of the data into another workbook, pdf? what is the sheet name? Is this "Call Scheduled for" a named range or cell?

 

[Kesh]

Thank you Trevor. I just worked out a way to do it on Ms Access using DateDiff

But to answer your question though. Yes stale is for old, the only criteria is records older than 72 hours. The spreadsheet can be attached as an excel document or .pdf. "Call Scheduled for" is a cell

Thank you again

Kesh

 

[Trevor G]

Ok Kesh,

What I have done is worked out the code to do a filter and then copy the visible cells to another workbook then send that workbook as an attachment.

I have attached a copy for you to review. What I have done is used 2 date columns, 1 with the stored date then the next with =Now() to always represent the system date and time, I have then used a helper column to convert the dates into hours, based on that column I can complete the task. You would have to adapt the code of course to work for you, but see how you get on.

I have saved the workbook as a 2003 version.

 

[Kesh]

Thank you Trevor