How to convert a decimal into it's time equivalent as part of a function?
I'm running into an issue when trying to compare data across two sheets to find discrepancies - specifically when it comes to comparing start and end times.
Right now, the "IF" statement in my screenshot is executing perfectly, except when a time is involved - it's reading those cells as decimals instead (but only sometimes).
I've tried formatting these cells (on the raw data AND on this "Discrepancies" report sheet) so that they are displayed as a "HH:MM am/pm" time, but the sheet is still comparing the decimal values.
Is there anything that I can add to this function to account for a compared value being a time instead of text, and having that text be compared for any discrepancies? I cannot add or change anything to the raw data sheets, the only thing I can edit is the formula seen in the screenshot I provided.
See the highlighted cells in my screenshot - this is the issue I keep running into. As you can see, there are SOME cells (the non-highlighted ones) that are executing as intended, but I'm unsure why this isn't the case for the whole spreadsheet when I've formatted everything the same way using the exact same formula across the whole sheet.
For example, the values in cell N2 is "8:00 AM" on both sheets, so the formula should just display "8:00 AM" in that cell (and NOT be highlighted) since there is no discrepancy in the cells between both sheets it's comparing. But instead, it's showing both times as a decimal with the slightest difference between them and is suggesting a difference where there technically isn't (or shouldn't be) one.
Please help!
Screenshot of original spreadsheet for reference
---EDIT (added the below):
Here is a view-only version of a SAMPLE SHEET that displays the issue I'm having: https://docs.google.com/spreadsheets/d/1BdSQGsCajB3kOnYxzM3sl-0o3iTvR3ABdHpnzYRXjpA/edit?usp=sharing
On the sample sheet, the only cells that are performing as intended are C2, E2, G2, I2, K2, K6, or any cells that contain text like "Closed". Any of the other cells that have a time in both raw data tabs appears to be pulling the serial numbers for those times instead of correctly formatting it into "HH:mm AM/PM".
A quick tour of how the SAMPLE SHEET is set up:
- User enters raw data into the "MicrositeRawData" and "SalesforceRawData" tabs.
- Data is pulled from the "SalesforceRawData" tab into the "CleanedUpSalesforceData" tab using a QUERY that matches the UNIQUE ID's from the "MicrositeRawData" sheet, so that it essentially creates a tab that's in the same order and accounts for any extraneous data between the tabs (keep in mind this is a sample sheet and that the original sheet I'm using includes a lot more data which causes a mismatch of rows between the sheets which makes the QUERY necessary).
- The "DISCREPANCIES" tab then compares the data between the "MicrositeRawData" and "CleanedUpSalesforceData" tabs. If the data is the same, it simply copies the data from the "MicrositeRawData" cell. But if the data is NOT the same, it lists the values from both sheets and is conditionally formatted to highlight those cells in yellow.
- If there is data on the "MicrositeRawData" tab that is NOT included on the "SalesforceRawData" tab, the "DISCREPANCIES" tab will notate that and highlight the "A" cell in pink instead of yellow (as demonstrated in "A5").
try in B2:
=IF(MicrositeRawData!B2=CleanedUpSalesforceData!B2, MicrositeRawData!B2,
"MICROSITE: "&TEXT(MicrositeRawData!B2, "h:mm AM/PM")&CHAR(10)&
"SALESFORCE: "&TEXT(CleanedUpSalesforceData!B2, "h:mm AM/PM"))
update
delete all formulae from range B2:O10 and use this in B2:
=ARRAYFORMULA(IF(TO_TEXT(MicrositeRawData!B2:O10)=
TO_TEXT(CleanedUpSalesforceData!B2:O10), MicrositeRawData!B2:O10,
"MICROSITE: "&TEXT(IF(MicrositeRawData!B2:O10="",
"", MicrositeRawData!B2:O10), "h:mm AM/PM")&CHAR(10)&
"SALESFORCE: "&TEXT(IF(CleanedUpSalesforceData!B2:O10="",
"", CleanedUpSalesforceData!B2:O10), "h:mm AM/PM")))
- Conversion operator and bool operator for if init expression
- MATCH formula with multiple conditions returning true when false
- Mysql How to only select from a column if the column exists
- Unable to use IF statement in plpgsql
- Trouble Excluding Named Worksheets from a Loop
- Adding only some data labels and a total value at the top of my stacked bar chart
- Good C-coding style for multiple lines if conditions
- If statement to check if an array is not in another array
- Determining if a string contains a word
- How can I make a def function with my "if" statements?
- How to assign a variable in an IF condition, and then return it?
- Using or in if statement (Python)
- C# Creating a Square
- Return TRUE/FALSE column for checking if a set of columns contains a value/string
- Does Go have "if x in" construct similar to Python?
- Does if (myBool == true) differ computational wise to if(myBool)?
- Write a Basic Car rental program on java / problems
- Writing a "Chessboard" function in Python that prints out requested length of binary
- Will my program execute "else" even if it executed my "if"?
- Why Java does not see that Integers are equal?
- How to have the Xcode 3.1 compiler warn of assignment operator in an if statement?
- Nested if Statement good or bad?
- How to write if statement in .tmux.conf to set different options for different tmux versions?
- is not null Vs != null (Unity C#)
- VBA "does not equal" code not stepping through IF statement as expected
- Avoiding nested if else statement ladder in Python
- I want to take output from a function to use in a IF statement in another function
- What actually happens when we put conditions without [] (brackets) in if else block in a bash script?
- C++ The if statement is still true even when only one condition is met
- Fortran if statement errors