Date math outputting number as text?

Kemidan2014

Board Regular
Joined
Apr 4, 2022
Messages
229
Office Version
  1. 365
Platform
  1. Windows
I have 2 Query expressions to output a value to show how many days something is late or left until due.
When I run the query the DaysPlanOD number values are on the right side of their field
but DAYSActOD values are all on left side of the field.
Neither of these are fields in any tables just a math expression in the query itself.

I confirmed all fields involved are "Date/Time" structured but when I export the query to Excel DaysPlanOD is a Number but DaysActOD is NOT a number and this is the value I wanted to graph. Why would this be? I know I can manually go in and convert it to a number but the idea was to be hands free for the user so what do I need to change to get DaysActOD to be a number?

DaysPlanOD: IIf([ltcmPlanSubmitted] Is Null,Date()-[LTCMPlanDue],[LTCMPlanSubmitted]-[LTCMPlanDue])

DaysActOD: IIf([OriginalLTCMActualDueDate] Is Null,"",IIf([LTCMActualSubmitted] Is Null,Date()-[OriginalLTCMActualDueDate],[LTCMActualSubmitted]-[OriginalLTCMActualDueDate]))
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I think it is because your "DaysActOD" field can return "", which is considered a text value.
Try returning 0 instead, i.e.
Rich (BB code):
DaysActOD: IIf([OriginalLTCMActualDueDate] Is Null,0,IIf([LTCMActualSubmitted] Is Null,Date()-[OriginalLTCMActualDueDate],[LTCMActualSubmitted]-[OriginalLTCMActualDueDate]))
 
Upvote 0
Solution
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,181
Messages
6,170,581
Members
452,339
Latest member
mrlong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top