Tag Archives: Time

Make impossible possible

Convert text of specific pattern like “1d 2h 3m 4s” into real time (26:03:04) in Excel To kick start the Year of Monkey, let’s challenge the apparently impossible… Do you think it is not possible in Excel?  If you do, you … Continue reading

Rate this:

Posted in Formula | Tagged , , , , , | Leave a comment

How to limit time interval input in a single cell?

Got the following question from a reader: Under data validation, is it possible for me to restrict the time duration (in a cell) to be 30mins or less? Example: 9.00 – 9.30 (accepted) 10.15 – 10.50 (rejected) Obviously, the answer … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | 4 Comments

SUM Time in the end of text strings

Did you encounter a task like this?  I did.  Actually it is not as difficult as you may expect.  It could be done in just a minute IF the data is not too bad, like the one shown above. I … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , , , , , , , | 6 Comments

Allow time input at 15-min intervals only – Data Validation

How to limit user to input time at 15-min intervals, i.e. 00:15, 00:30, 00:45, 01:00, etc.? We talked about how to limit user to input Time in the previous post.  Let’s go one step further with Custom Data Validation, where you … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Time Conversion (2)

How to convert 24hr input as 4-digit number into TIME in Excel? i.e. turn 500 into 05:00 correctly in Excel. Answer: =TEXT(Your 4-digit number,”00\:00″)+0 ‘Format the result as TIME

Rate this:

Posted in Excel Tips, Formula | Tagged , | 6 Comments

Time Conversion – How to convert 24hr input as 4-digit number into TIME in Excel?

How to convert something like 1200 into 12:00? For easy sake of input, we often input 24-hr time as four digit number like 930 for 9:30, 1815 for 18:15 etc.  However, if time is input in this way in Excel, … Continue reading

Rate this:

Posted in Excel Tips, Format | Tagged , , | 12 Comments

Do you need TIME?

To convert [hour:minute] into number of hours quickly. If you are working in HR field, you may need to calculate wages for part-time staff whose salary is paid on hourly basis.  So you need to convert 8:45 (8 hrs 45 … Continue reading

Rate this:

Posted in Formula | Tagged | 2 Comments