Display Particular Value if Number is greater than or equal (#) and Number is Less than or equal (#)

Hello

I am creating a Spread sheet to keep track of my time sheets. I was wondering how to create a formula that will display a certain number if that number is greater than 4 and that same number is less than 7.

At my work Monday to Friday we are paid extras for Morning , Evening and Night Shifts

Morning Shifts must be starting from 4AM to 7AM

Day Shift Commences at or after 7AM and Finishes at or before 6PM

Evening Shifts Commence If you have worked At or past 6PM up until Midnight

Night Shifts Is if your shift finishes after midnight.

I want the TOTAL(DECIAML) value of hours to display in the particular category if it meets that particular shift criteria in Start and or finish.

E.g I would like 7.25 to display in Morning Of Monday as the Start time was before 4AM but no later than 7AM, But no other

I would like 6.50 to display in Morning of Tuesday as the Start time was before 4AM but no later than 7AM, But no other

I would like 5.75 to display in Afternoon of Wednesday as it satisfies finishing the shift after 6PM but no later than 12AM (00:00), But no other

Find more posts tagged with

Accepted answers

All comments

Mark Cronk

Hi @Mikaere Flavell ,

For your morning calculation try:

=IF(value(left([start time]@row,2))>=7, 0, (MIN(VALUE(LEFT([finish Time]@row,2), 7)) + (VALUE(RIGHT([finish Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))

Does that work? It requires all times to be in the format XX:XX with 2 digits before and after the : . The formula says if the start time is after 7 then the value is 0. Then it calculates the time between the start and finish time or start and 07:00.

@Paul Newcome is the guru of all things time. Bookmark his post below for reference. Using it you can probably figure out night and afternoons.

Mark

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time/p1

Mikaere Flavell

Hey Mark

Thank you for your swift response, I tried using that but still somewhat confused and not working correctly.

H7 (Yellow) needs to display to display G7(Green) if it D7(Blue) is less than or equal to 07:00 and greater than or equal to 04:00

Thanks for your help

https://community.smartsheet.com/discussion/comment/289752#Comment_289752

Mark Cronk

Hi Mikaere,

When you enter this formula into your Morning column what response do you get?

=IF(value(left([start time]@row,2))>=7, 0, (MIN(VALUE(LEFT([finish Time]@row, 2)), 7) + (VALUE(RIGHT([finish Time]@row, 2)) / 60) - (VALUE(LEFT([Start Time]@row, 2)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))

Mark

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.