0

I've been asked the following question as a test:

Cell A1: 28.12.2018 - 09:00 111,1 02.01.2019 - 08:00 22222,22 08.01.2019 - 23:56 3333,33 20.01.2019 - 00:00 44,4 28.01.2019 - 00:00 55,55 08.02.2019 - 00:00 6666,66 15.02.2019 - 00:00 77,7

Same criteria is in cells A2, A3 and so on.

Given today's date 06.02.2019 (we can use now() or today() ), create a formula in B1, so that it will pull out a number corresponding to the date and time, 55,55 in our case (those dates represent categories, for all dates between 28.01.2019 00:00 and 08.02.2019 00:00 the value should be 55,55).

My logic when trying to solve this was to somehow use the MID function along with FIND() to return a specific text string (line) but I'm not sure on how to find the line corresponding to the current date. I don't even know where to start with that to be honest.

Thanks in advance!

user11021850
  • 1
  • 1
  • And why exactly does it return `55,55` or `6666,66` respectively when searching for two dates that aren't even in your data? Please eleborate and update your question with what you have tried so far that didn't work. – JvdV Feb 06 '19 at 09:31
  • @JvdV thanks! I've added a few more comments. Hope this helps. – user11021850 Feb 06 '19 at 09:43
  • @JvdV the data represents groups for when the imaginary index takes on a certain value. For example, if it's 21.01.2019 it should take on 44,4, while if it's 30.02.2019, if should be 55,55 and so on. The main question for me is how to link the current date (and time too I guess) to this cell in order to find the corresponding piece of text string. My bad if it's a mouthful. – user11021850 Feb 06 '19 at 09:56
  • You might want to look into regular expression, explained [here](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). Or possibly a UDF (User Defined Function). – JvdV Feb 06 '19 at 10:06
  • Welcome to SO. I'm sorry but i do not understand the process. You said `while if it's 30.02.2019, if should be 55,55`. May you explain why? you have the data `15.02.2019 - 00:00 77,7` Why if date is 30.02.2019 the data is 55,55 instead of 77,7? – Foxfire And Burns And Burns Feb 06 '19 at 11:47
  • @FoxfireAndBurnsAndBurns thanks. I just realized I made a typo. It should've been 30.01.2019 – user11021850 Feb 06 '19 at 11:52

0 Answers0