CODEWITHSUNDEEP
excel-formula
John Musgrave
John Musgrave

Reputation: 17

Excel formula with times in 1904 date system

In Excel I have three columns, say A, B and C, all with the background format of hh:mm:ss and the 1904 date system, meaning that I can do simple arithmetic across the columns.

Cells in columns A and B will mostly be populated with times but can also be either blank or have text in them.

Using cells A1, B1 and C1 as an example I need a formula in C1 which says if both A1 and B1 have times within them then perform the sum A1 minus B1 into C1. So if A1 is 01:07:26 and B1 is 01:08:26 the resultant sum in C1 will be -00:01:00. If blank or text appears in A1 and/or B1 then the arithmetic function will not be performed.

I have been going round in circles with SUMIFS and have not managed to figure out a formula which will do the trick so if anyone could help me I would be very grateful.

Upvotes: 0

Views: 391

Answers (1)

Tom Zych
Tom Zych

Reputation: 13576

I don't have Excel on this machine, but a more fruitful approach would be something like:

=IF(AND(ISNUM(A1),ISNUM(A2)),A1-B1,"")

If you need more fine-grained checking for a time, see this post.

Upvotes: 1

Related Questions