Using Time in formulas

**JakeMann** · 02-07-2013, 07:07 AM

Hi All,

I need a bit of help with a formula involving time.

In column H of the 'Formula' page a I have formula that looks at the dates in column D of the 'Week' page and returns a numerical list.

This is the formula -=IF(OR(E2=Week!$D$2,E2=Week!$D$3,),COUNT(H$1:H1)+1,"")

I'm not sure if it's because the date also lists a time, but what I want it to do is list everything for todays date up to 12pm the following day.

Is there a way for it the date in the 'Week' page to look from 00:00am of today to 12:00pm the following day?

**TMS** · 02-07-2013, 07:39 AM

The problem is that the value in E2 is coming across as text. Change the formula to: =--Intouch!EA2

Then, in H2, put: =IF(AND(E2>=Week!$D$2+TIME(0,0,0),E2<=Week!$D$3+TIME(12,0,0)),COUNT(H$1:H1)+1,"")

Copy both formulae down.

Regards, TMS

**JakeMann** · 02-07-2013, 07:43 AM

That's brilliant, thank you. So by put a -- converts text to a date...? Is that correct?

**TMS** · 02-07-2013, 08:41 AM

You're welcome.

So by put a -- converts text to a date...? Is that correct?

Not exactly, it coerces a number stored as text to a true number. A date is just a number representing the number of days from 1 January 1900 and a time is a number representing a fraction of a day. So, add a real date and time together and you get a number. Previously, I think you were comparing text to numeric.

Anyway, short story, by making sure all the numbers are real numbers, the comparisons work as you expect them to.

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.