98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Excel deduct TIME
Excel deduct TIME
Resolved · Medium Priority · Version 2003
Steve has attended:
Project Management - Framework & Processes course
Excel Advanced course
Excel deduct TIME
Hi, how can I deduct 2 hours from a range of times? I want all of the following times to show two hours earlier. Thanks
Start time Stop time
12:43:37
0:14:41 0:14:42
2:52:42 2:52:43
4:22:37 4:22:38
4:43:30 4:43:31
6:08:10 6:08:11
8:17:43 8:17:44
8:20:15 8:20:16
8:23:41 8:23:42
8:25:23 8:25:24
8:26:25 8:26:26
8:26:58 8:26:59
8:27:57 8:27:58
8:32:24 8:32:25
11:27:18
RE: Excel deduct TIME
Hi Steve,
I popped your figures into a worksheet and selected the two columns adjacent, I put the following formula into cell C1, after formatting the range as HH:MM::SS.
=A1-(2/24)+2 then used CTRL+Enter to apply the formula to the selected cells. If you wished to replace the existing data, it would probably be expedient to incorporate the formula into a macro with the macro then replacing the existing data as the next step. Note: it causes a circular reference to run the formula in A1. I hope that helps
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Working with 3D formulasYou can only use fill-down if you remove $ for Absolute References. |