#VALUE! error in excel when trying to find the difference between 2 date/time cells

Question

#VALUE! error in excel when trying to find the difference between 2 date/time cells

Shams Mahmud 100

Hello,

I am trying to calculate the work duration (in mins) for a job. The following snippet shows selected rows for Cols K:M. The formula in Col K & L are using text and concat functions to create a date and time cell. Col M is simply calculating the difference and converting it into minutes:

User's image

The above formula references cell K4196. The following snippet shows the formula in cell L4196:

User's image

the last snippet references cell M4196 and shows the calculation for minutes:

User's image

The frustrating part is that when I view this file from my MSFT Edge browser the formula in Col M is returning with the calculated value. It's only when I download the file in MSFT Excel that I get the #VALUE! error. Lastly, the current format in Cols K and L is Time. Also, the format for cell M is number with an active Data Validation rule to allow only Decimal with Min and Max values between -1,000,000 and 1,000,000.

I am wondering if there is a workaround. I tried to enclose the cell references in Col M with Value() but to no avail. I also tried to remove the spaces in the formulas under Cols K and L. Can you please provide with any text manipulation tips so that I can calculate this correctly within MSFT Excel.

Regards,

Shams.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Shams Mahmud,

Based on your sharing, the issue is that the data in your column K and L are text strings, not real Excel date/time values. In your formulas, you use TEXT() formula to convert the date and time into text like "01/24/2026 04:00:00". Excel can display the data nicely but since it's not a numeric datetime data, the time subtraction would fail as expected.

Moreover, the formula in cell M4196 =IF(K4196="", 0, L4196-K4196*24*60) has a wrong order of operations. Excel reads that formula as L4196 - (K4196*24*60) while your expected outcome should be (L4196-K4196)*24*60.

In this case, the best option is to store value in column K and L in read datetime values. However, there is a faster workaround is to use this revised formula instead in cell M4196:

=IF(K4196="",0,(VALUE(L4196)-VALUE(K4196))*24*60)

Please try the formula and let me know if it works for your situation. If not, please notify me and we can work together to find a solution.

If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Shams Mahmud 100 Reputation points

2026-06-11T00:13:28.37+00:00

Hello Hendrix,

Thank you for your message. Unfortunately when I try to use the workaround formula, I get the following data validation error:

Here is a snippet of the Data Validation rule:

This rule is being applied to Cell M4196. I cleared the above data validation rule from M4196 and I still got the #VALUE! error. Hope you can nudge me towards a solution. Thank you.

Regards,

Shams.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-11T22:52:49.9933333+00:00
Hi @Shams Mahmud,

Greatly appreciate your update.

Since the VALUE() formula depends on the system's date/time format settings which make it's not consistent between different environments, using VALUE() may be not an identical approach for your situation.

In that case, for a better and simpler setup, I suggest you should modify the formula in column K and L to store them as real date values, not as text:

In column K, instead of using TEXT, just sum date and time normally with =IF($B4196="","", $B4196+$I4196)). This will store the result as date value (e.g for row 4196 in your screenshot it will be 46046.1667).

Then right-click the cell > Format cell > choose Custom and manually type the date time format mm/dd/yyyy hh:mm:ss there.

Use this formula in column L =IF(ISBLANK($C4196), $B4196+$J4196, $C4196+$J4196) then use the same step to format the cell in mm/dd/yyyy hh:mm:ss

For duration calculation, use this formula =IF(K4196="",0,(L4916-K4196)2460). It will work properly even if you apply the validation rule there.

You can give it a try and see if it meets your needs. If not, don't hesitate to reach out again and we can work together to find the best approach for your concern.

Thank you for your patience and understanding. I'm looking forward to your reply.
Shams Mahmud 100 Reputation points

2026-06-12T15:06:48.2166667+00:00

Hi Hendrix,

Thank you for the revised formulas for Cols K & L. I was able to get rid of the #VALUE! error in Col M. For Col M, I think you meant to say: if(K4196="",0,(L4916-K4916)2460). SImply leaving the last entry as a constant 2460 gives me incorrect answer. Once I switch to the above I get the right answer
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-14T15:30:20.3266667+00:00

Hi @Shams Mahmud,

Thank you for your updates and I'm sorry for my mistake since I forgot to put the formula into inline codeso the number 24 between two * became italics text. The correct formula is =IF(K4196="",0,(L4916-K4196)*24*60).

That said, I'm very glad to know that my sharing would help resolve your concern.

For any concerns or questions in the future, please don’t hesitate to reach out again. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

Wishing you a wonderful day ahead.