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

Options

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.🙏

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 06/01/24 Answer ✓
    Options

    @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

Answers

  • Celenne Damian
    Options

    @Kelly Moore do you have any idea?

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    @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
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 06/01/24 Answer ✓
    Options

    @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
    Options

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

  • Celenne Damian
    Options

    @Kelly Moor Thank you so much!!!!

  • Celenne Damian
    Options

    @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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!