date time manipulation

[if_Excel]

i have dates and times like 23 May 2011 06:00 , 23 May 2011 10:27 and 22 May 2011 18:00 in a column. values corresping to these date and time are in another column. i am trying to manipulate the date and time with a formula so that i could select the right value for the date. Direct date time conversion did not work. so i converted the date time using Timevalue() and then use a formula like IF(AND(C2>=B2,C2<=A2),F2,0). what i am doing is to define a time window to select the right value. but this is not working correctly because the timevalue() function returned for the date time is not correct. anyway all i want to do is to take a date / time window and return the value corresponding to that date time. any help is appreciated.

 

[p45cal]

i have dates and times like 23 May 2011 06:00 , 23 May 2011 10:27 and 22 May 2011 18:00 in a column.

If you select one of the above cells, right-click it and choose Format Cells…:
a) what Category is it showing as?
b) If you start to change it to General, what does it show in the Sample area? A number like say 40686.68889 or something else? You can cancel the dialogue box at this stage so as not to change anything.

I'm trying to establish whether the date/time is an Excel Date/Time or not.

Can you show us what kind of formula you had when you used TimeValue?

Also if you could post what your sheet looks like with the likes of Excel Jeanie (or MrExcel html maker), it would help make things very clear.

 

[if_Excel]

the date and time are generated as array from a query.right clicking on them doesnt reformat them. but the tiimevalue() function returns a numeric for example 5/23/2011 9:51:25 Am is returned as 0.410706019. waht i am doing is to see if that time fall between as start and end time which were generated by the TEXT and NOW functions. i think what is missing is that timevalue only covert the time and no the day. therefore it will return the same value for the same time yesterday as it will return for today.

 

[barry houdini]

TIMEVALUE function doesn't work on true time/date values, only on text values so your date/time must be text-formatted.....and as you surmised TIMEVALUE extracts only the time anyway so that probably isn't what you need.

Your time/date values look like a valid excel format so "coercing" using +0 or similar should work, e.g. try

=IF(AND(C2+0>=B2,C2+0<=A2),F2,0)

where C2 is the text-formatted time/date and A2 and B2 define the start and end of your time period

 

[if_Excel]

tried coercing with +0 didnt work. i will look up more coering parameters

 

[barry houdini]

OK, then you could try DATEVALUE and TIMEVALUE, e.g.

=DATEVALUE(C2)+TIMEVALUE(C2)

....and use that in your formula

 

[if_Excel]

thanks a lot that worked. i forgot about datevalue(). thanks again.