Sunday, May 03, 2009

Excel's IF function & TIMEVALUE

Tonight I was trying to create an IF function to calculate a value based on whether the time in one column was under 10am or not. When Excel help - didn't, naturally I googled. Someone had a similar query at one of those experts answer sites in 2007 but the single answer given to that query didn't help me either.

Luckily one of Microsoft's support pages had a somewhat related discussion from which I could pull a guess about the information I might need to use. I scanned as far as an example IF function using a value called DATEVALUE and proceeded to experiment.

My =IF(E92<TIMEVALUE("10:00:00"),D92,D92+1)

As you might be able to guess this is calculating a date within another time zone. Given that the countries in my sheet play with Daylight Savings, I've considered playing with additional IFs to define TIMEVALUES from Daylight Savings start/end dates - but those dates change from year to year! More fun in store :D


Related Posts Plugin for WordPress, Blogger...