Forum Discussion

yash___1312's avatar
yash___1312
Copper Contributor
Mar 29, 2025

Count Days Function help required

I can't seem to get this right. I have to count the number of days between two columns and I have tried many formulas but none of them seem to work. 

I have to count the number of calendar days between columns S and T, and all the formulas I tried give me #VALUE! as the output. 

I also tried changing the format but nothing seems to update the workbook. Apart from that "text to columns" feature is also not working. 

Does anyone have any tips on how to fix this issue? 

BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 31, 2025

    The date/time values are left-aligned, making me suspect that Excel sees them as text values.

    Try the following:

    • Select all cells with date/time values.
    • Press Ctrl+H to activate the Replace dialog.
    • Enter AM in the 'Find what' box.
    • Enter a space followed by AM in the 'Replace with' box.
    • Click 'Replace All'.

    Repeat the above steps with PM in the 'Find what' box and a space followed by PM in the 'Replace with' box.

Resources