Google-sheets – Find given time and date in named range

google sheetsgoogle-sheets-named-rangesvlookup

I have a Google Sheet with my wife where we both enter our upcoming events. They are included in named ranges and passed to the main sheet where the information is displayed. For example, I enter in the Event Scheduler: 01/01/2000 | 06:00 | Take the dog for a walk, where each | is a new cell. I then generate a datetime with say A1&A2, this usually comes out to be some number with a decimal like 442770.25 is 22/03/21 & 06:00:00. I've now tried the following commands:

=MATCH(TODAY()&E14,INDEX(MolliesEvents,A13:A1000,6),0),

where MolliesEvents is a list of Mollies Events organised as follows:

date | time | | description | unique ID | datetime so the TODAY()&E14 = 442770.479166666666666, and INDEX(MolliesEvents,A13:A1000,6) = [...,442770.479166666666667,...]

where the …'s are just other date&time combinations. Clearly, these two are the same, they subtract to zero, and checking their equality gives a TRUE. However, the command is not finding the given index and matching it. In fact the error given is that "Did not find value '442770.47916666666666' in MATCH evaluation. The second command is =vlookup(TODAY()&E14,MolliesEvents,6,0) with similar definitions and it is still unable to find the given locale. Any help?

Here: https://docs.google.com/spreadsheets/d/1JINVcTPlfjPK1SPoTDC9eKhpYzmG6daq3umrBn_8TP0/edit?usp=sharing is the link to a copy of the same google sheet so that you can work directly with the source.

Best Answer

You want to populate a calendar item list for a given day from another sheet containing a detailed list of all scheduled items. There may be more than one item scheduled for a given time period.

This answer requires a couple of minor changes to your layout..

Daily + Weekly

  • delete ALL the unnecessary rows (I found that row 32 was the necessary last row)
  • Column E - change the formula and format the cell as "Date time"
    • example formula: ='Time Intervals'!D4+$A$10
    • example format: 26/03/2021 06:00:00
  • Increase the column size of Columns F, G and H so that mutiple events can be seen.
  • Delete the existing formulas in Columns F, G and H

Event Scheduler

  • Unmerge "Description" columns. Presently each description occupies two merged columns. For example, descriptions for Mollie's events merges Column C and D.
  • Copy the description into the right-hand column. For example, for Mollies events, copy the description into Column D.
  • In the left-hand column (Column D for Mollies Events), enter a formula to combine the date and time columns (such as =A14+B14), and format cells as "Date time".

Formula

This formula is an example for Mollie; it can be editted for suit Ayden and the dog

Enter this formula in sheet="Daily + Weekly", cell F3. The results will populate all the relevant rows/times for Mollie.

=ARRAYFORMULA({TRANSPOSE(SUBSTITUTE(TRIM(QUERY(IF(TRANSPOSE(E3:E)='Event Scheduler'!C14:C,'Event Scheduler'!D14:D&CHAR(10),),,9^99)),CHAR(10),","))})

Daily and Weekly

daily

Event Scheduler

scheduler

Related Topic