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?
 
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

Similar Threads

separating date and time 5
HELP! I need to make an IF Statement comparing date/time fields.. 2
date and time difference 19
Date and Time Conversion 3
Date format problems after csv import 2
Need help with converting CUSTOM format/TEXT format to DATE format 6
Format duration in mm/dd hh:mm 3
Getting MM-DD HH:MM out of Julian date 4

Top