Time value result in on cell used in IF function in another not working

kaia2001

New Member
Joined
Sep 7, 2018
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Time ranges from below to be compared in IF stated with column [@TIME]:

1ST=5:30 AM to 13:29 PM
2ND=14:00 TO 22:29 PM

The formulas in the Number column and the Week column will not work properly. How do I turn the info in the Time column into the time format I need to use in the Number Column to determine if the time falls between the two logical test?
Number
WEEK
DATE
TIME
MODIFYTS
FORMULA:
=IF(AND([@TIME]>TIMEVALUE("05:30:00"),([@TIME])<=TIMEVALUE("13:29:00")),"1st","")

CELL OUTPUT: blank
FORMULA:

=WEEKNUM([@DATE]+553,2)

CELL OUTPUT: "51"
FORMULA:

=IFERROR(TEXT([@MODIFYTS]-TIME(8,0,0),"mm/dd/yyyy"),"")

CELL OUTPUT: "06/08/2020"
FORMULA:

=IFERROR([@MODIFYTS]-TIME(8,0,0),"")

CELL OUTPUT: "7:06:00 PM"
CELL CONTENTS: 6/9/2020 3:06
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

kaia2001

New Member
Joined
Sep 7, 2018
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
Screenshot of the spreadsheet is attached. The time column i converting to PST and the highlighted column should show if the time in the "TIME" column is 1st shift 2nd shift or 3rd.
 

Attachments

  • Putaway screenshot.jpg
    Putaway screenshot.jpg
    138.6 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,479
Members
412,596
Latest member
nickthebizz
Top