Working with Date/Time


T

Thanks

Hello
I am creating a date/time using
=TEXT(DATE(year,MONTH(B10),D10),"yyyy-mm-dd ")&TEXT(TIME(E10,0,0),"hh:mm")
to produce 2009-01-21 00:00
I would like to compare that to a start time (AA10) and stop time (AB10) to
see if it falls withing the range. The start/stop times are downloaded and
formatted as yyyy-mm-dd hh:mm TZ such as 2009-01-21 00:00:00 ED.
How would I do this?
 
Ad

Advertisements

L

Luke M

Since your end result is a numerical comparison, rather than creating a text
string, change your original formula to:
=DATE(year,MONTH(B10),D10)+TIME(E10,0,0)
and format cell as yyyy-mm-dd hh:mm

You're now able to compare this more easily to other numbers. To modify your
start/stop times, you can use this formula:
=VALUE(LEFT(AA10,19)

This will strip out the data & time, and then convert it to a value/number,
and you can compare it to your original number.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top