I need to join two databases (Excel tables). Each one has identification card numbers (unique) and time of admission. The main key is the id of the patient. I only want to get the ones with the same id, and time of admission less than 9 hours ago.

TABLE 1: Patient id    Date and time of admission
855                    22/01/2018 15:50
866                    28/01/2018 16:45
877                    30/01/2018 22:40
888                    01/02/2018 00:21

TABLE 2: Patient id    Date and time of admission
855                    30/01/2018 01:22
877                    31/01/2018 03:11
888                    06/02/2018 05:11

JOIN TABLE (ids <9 hs) Date and time (1)    Date and time (2)
877                    30/01/2018 22:40     31/01/2018 03:11

It would be better with a query because: 1. This database is changing every day, 2. I need to extract the joint database and import to SPSS each month

Something like IF(patientid1=patientid2) & IF(dateandtime2-dateandtime1>9:00)

  • What have you tried so far? Please explain the steps and where you are having the problem? – Bharat Anand Feb 12 '18 at 5:05
  • So far, I have made a query for the same id number to join both tables (maybe there is something wrong because the number of regs is higher than the regs in the big table), and with this query, I created an IF(date1-date2<9hrs) then color green, and extend to all regs. – Nicolas Riera Feb 12 '18 at 8:24
  • whats your query, edit your question to show that – PeterH Feb 12 '18 at 11:25
  • Make it clear,,, you need Conditional Formatting or Query to pull records ? – Rajesh S Feb 12 '18 at 11:38
  • It would be better a query, because I have to export to SPSS the joint table to make some statistics. – Nicolas Riera Feb 12 '18 at 13:14

On Table 1 you can VLOOKUP to Table 2 based on Patient Id and bring back the secondary admission time (I will call this Admission Time 2). You can then add another column and fill it with a formula, Admission Time 2 (minus) Admission Time 1. You can filter to see which rows are less than 9 hours (Admission Time 2 minus Admission Time 1 may end up being a fraction of a day, in which case you would need to multiple the result by 24 to view it in hours).

| improve this answer | |
  • Thank you very much!!! I would prefeer a query, because this database is changing every day, so I can perform this query, extract the joint database and import to SPSS. Sorry for not explaining better before. – Nicolas Riera Feb 12 '18 at 13:39
  • I understand but if Excel is the only way to access this 'database' this may not be feasible. This type of thing is a limitation of Excel and one of the benefits gained by moving to a SQL-supported database system like MySQL or SQLite. Microsoft Access can even perform a query powerful enough to do what you are asking. – mlhDev Feb 12 '18 at 13:42
  • Thanks @mlhDev . If I import those tables to Access, what could I do? I'm not a specialist in Ms Access, but I know how to import tables from excel. – Nicolas Riera Feb 12 '18 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.