working with hh:mm:ss formats and IF statements

anotherrachel

New Member
Joined
Mar 8, 2010
Messages
5
Hello
I am trying to write an IF statement using date/time/hh:mm:ss data and would appreciate some help.
I have ~ 150,000 rows of data so am keen not to review line by line.

I have an extract of data with a date time stamp and I have calculated the difference between two times.
eg:
I have subtracted the first time stamp 29/10/2019 7:14:57 AM from the second time stamp 2
9/10/2019 7:20:00 AM.
The answer with the format h:mm:ss is 0:05:03 (eg in cell c10)

In another cell, I would like to have an IF statement to group the time into 3 buckets
anything > 20 minutes = "long break", anything < 5 minutes = "working", all other = short break

=IF(c10<5,"working",IF(c10>20,"long break","short break"))
In the example above, I would expect the result would be "short break" but it does not seem to work.
I assume because the numbers 20 and 5 are incompatible with the 5:03 minutes above.

Does anyone how I can make the IF statement work?

Thank you!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe (untested)…

=IF(C10<TIMEVALUE("00:05:00"),"working",IF(C10>TIMEVALUE("00:20:00"),"long break","short break"))
 

anotherrachel

New Member
Joined
Mar 8, 2010
Messages
5
Thank you - yes it does work!
Much appreciated as I spent an hour pulling out my hair before I posted!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,322
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
No problem and welcome to the board :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,384
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top