dimanche 31 janvier 2016

How to write an IF statement in excel for times and dates in the same cell

I am trying to find out if an event occurs between two other events.

For all three events, I have the date in one column, the time in another column and then the date followed by the time in a third column. Columns three is formatted like this =TEXT(B1,"dd/mm/yyyy ")&TEXT(A1,"hh:mm:ss")

The table looks like this:

  |   a   |   b   |    c    |
_____________________________
1 | time  | date  |time/date|
2 | time  | date  |time/date|
3 | time  | date  |time/date|

Row 1,2 and 3 are each event. Let's say we're trying to work out if event 3 occurs within event 1 and 2. The formula I have so far goes in D1 and looks like this:

=IF(AND(DATEVALUE(C3)>=DATEVALUE(C1),DATEVALUE(C3)<=DATEVALUE(B3)),TRUE,FALSE)

I have various different sets of events that I want to check in a similar way, but it's not working for me. I have made sure that each cell I'm referring to is formatted correctly i.e date is dd/mm/yyy and time is hh:mm:ss etc but it's not working for some reason.

Haven't found an answer to this anywhere so I would really appreciate some help.

Thanks in advance!

Aucun commentaire:

Enregistrer un commentaire