How I can make a master document that find me if the info was inserted or not

Celenne Damian

I have a document that need to be inserted by format every 30 minutes, I want other document where I can see all days of the year with the rage of time , I just need to know if the information was inserted or they missed

This document is where the operator inserted the information

the next document is what I need to see if is there is any data from every day and every 30 minutes

is any formula that I can use to compare?

I be honest and I used several types of formulas and no working, I need help please.🙏

Kelly Moore

@Celenne Damian

In your other post, we separated the date and time from your Created column. To make this work as a total solution, it will be helpful if the separated Time was actually a rounded time value instead of the actual time. Or, add an additional helper column if you need that actual time.

To create the rounded time so that it will match your second sheet, the formula below evaluates the minutes portion of the Created date to see if the minutes are between 30 and 60. It inserts either a 00 or 30 based on that evaluation.

=TIME(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - 1 - FIND(" ", Created@row)) + ":" + IF(OR(VALUE(MID(Created@row, FIND(":", Created@row) + 1, LEN(Created@row) - 3 - FIND(":", Created@row))) = 0, VALUE(MID(Created@row, FIND(":", Created@row) + 1, LEN(Created@row) - 3 - FIND(":", Created@row))) > 30), "00", 30), 1, 3)

Once this data exists in your top sheet, change (if needed) the range of your COUNTIFS time column to match that Time column.

Does this make sense?
Kelly

Celenne Damian

@Kelly Moore do you have any idea?

Kelly Moore

Hey @Celenne Damian

Let me see if I understand what you're looking for. You have a sheet (top screenshot) that is receiving data every 30min. (Datashuttle from a .csv or xlsx?). You're trying to set up a second sheet that dynamically adds a row to match your first sheet so you can pull info from the first sheet?

Did I understand correctly? And what type of license do you have (Enterprise, etc)- in other words, do you have access to any of smartsheet's premier apps?

Kelly

Celenne Damian

the first picture is of a document where we can see the input that the operators inserted by a form and I can see Created date where I can split Date and Time

as you can see in the example they don't insert the information exactly every 30 minutes but they have the rage of during this 30 minutes to inserted

05/31/24 10:35
05/31/24 10:04

05/31/24 9:02
05/31/24 8:34

in the second picture is the document where I want to identify if they missing any data enter

05/31/24 10:30 complete
05/31/24 10:00 complete
05/31/24 09:30 missing
05/31/24 09:00 complete
05/31/24 08:30 complete

the thing is what kind of formula can help me to compare the 2 documents?

Kelly Moore

@Celenne Damian

Try this

=IF(COUNTIFS({3.- Can Filler Quality Check Sheet Date}, Date@row, {3.- Can Filler Quality Check Sheet Can Code Time}, TIME(@cell,1,3) = TIME([Time 2]@row, 1, 3)) =0, "Missing Data")

Remember you will have to manually create the cross sheet references - you cannot simply copy paste this formula

Will this work for you?
Kelly

Celenne Damian

the original problem is that we have a document where the operators inserted information every 30 minutes but this information is not always exactly every 30 minutes

example:

5/31/2024 00:05

-

5/31/2024 01:16

5/31/2024 01:38

they can inserted between the <00:00 >00:30

Then we want to create the second document where we can see all of days and all 30 minutes of the day, we want to know which is missing

example:

5/31/2024 00:00

5/31/2024 00:30

5/31/2024 01:00

what kind of formula can help to compare the data with these values date and time?

Kelly Moore

@Celenne Damian

In your other post, we separated the date and time from your Created column. To make this work as a total solution, it will be helpful if the separated Time was actually a rounded time value instead of the actual time. Or, add an additional helper column if you need that actual time.

To create the rounded time so that it will match your second sheet, the formula below evaluates the minutes portion of the Created date to see if the minutes are between 30 and 60. It inserts either a 00 or 30 based on that evaluation.

=TIME(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - 1 - FIND(" ", Created@row)) + ":" + IF(OR(VALUE(MID(Created@row, FIND(":", Created@row) + 1, LEN(Created@row) - 3 - FIND(":", Created@row))) = 0, VALUE(MID(Created@row, FIND(":", Created@row) + 1, LEN(Created@row) - 3 - FIND(":", Created@row))) > 30), "00", 30), 1, 3)

Once this data exists in your top sheet, change (if needed) the range of your COUNTIFS time column to match that Time column.

Does this make sense?
Kelly

Celenne Damian

@Kelly Moore you are a genius!!! it is exactly what I need

Celenne Damian

@Kelly Moor Thank you so much!!!!

Celenne Damian

@Kelly Moore , I hope you are well, do you know how I can change the Created date to Military time? or how I can adjust the formula to match with the times, because the formula not recognized the AM and PM time

here the formulas that I use

=TIME(MID([Created Date]@row, FIND(" ", [Created Date]@row) + 1, FIND(":", [Created Date]@row) - 1 - FIND(" ", [Created Date]@row)) + ":" + IF(OR(VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, LEN([Created Date]@row) - 3 - FIND(":", [Created Date]@row))) = 0, VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, LEN([Created Date]@row) - 3 - FIND(":", [Created Date]@row))) < 30), "00", 30), 1, 3)

=[Date From Created date]@row + " " + [COUNTS IFS MASTER DATA]@row

Can you please help me?

Thanks a lot!

Kelly Moore

Hey @Celenne Damian

Nice to hear from you. There isn't a way, to my knowledge, to format the system columns to a different format- is that what you're asking? However, we were converting the Time column to military time? One can control that format. Are you wanting it to be in 12hr format?

https://help.smartsheet.com/function/time

Are you saying the Time column isn't recognizing whether it's AM or PM? If that's the case, then let's try this

=TIME(MID([Created Date]@row, FIND(" ", [Created Date]@row) + 1, FIND(":", [Created Date]@row) - 1 - FIND(" ", [Created Date]@row)) + ":" + IF(OR(VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, LEN([Created Date]@row) - 3 - FIND(":", [Created Date]@row))) = 0, VALUE(MID([Created Date]@row, FIND(":", [Created Date]@row) + 1, LEN([Created Date]@row) - 3 - FIND(":", [Created Date]@row))) < 30), "00", 30)+RIGHT(Created Date]@row,3), 1, 3)

I didn't test this so fingers crossed

Kelly