Not really sure what you are asking.
Do you want to display the actual time (without the date) instead of the number? That can be done easily with the TimeSerial() function.

TimeOnly: TimeSerial (Hour (MyDate), Minute (MyDate), Second (MyDate))

Some screenshot may help us to understand your needs better.
The following all deal with times:

Time & Billing
Time as a Duration
Timesheets
Timestamp

There's also TimeValue(mydate)

Hi Kerry, you need some kind of Shift table that stores start date/time and end date/time of each of the 6 shifts in a week.  Then it would be a simple use of DLookup() to look up what shift matches a given date and time.  Naturally you need to use full date/time info in all your values and variables for this task.  If you need to keep historical records of the shifts, include the year info in the date/time values too.  That way, there would be no mistaking of one shift for another:

Let me try to clarify - instead of making "ShiftName" a field that I have to enter on each task record (meaning, what shift did this task get completed on), I realized that with the "Completed" DateTime already entered on the record, the shift that it was completed can simply be calculated. But that is WAY easier said than done. Believe it or not I actually got it to calculate correctly but the formula is an absolute monstrocity. I'll copy and paste it at the bottom so you can try it.

So let's imagine a company that has 3 shifts, Day, Mid, and Night shift. But each crew only works half the week, front half or back half, and on Wednesdays both front half and back half work together all day. That leaves you with 6 total shifts: Front half days (FHD), Front half mids (FHM), Front half nights (FHN), and the same for back half - (BHD, BHM and BHN).

Now imagine the first shift of the week starts X hours after midnight. In my case, the first shift of front half days starts at 7:0AM, or 7 hours after midnight. Using IIF statements with date and days of the week work a lot better if you start Sunday at midnight, but we don't. So I cheated and offset the week by 7 hours by subtracting that amount from the actual DateTime value. After that I used a labyrinth if nested IIF statements in the query to make it work. I called the new field "Shift". Here is the function I used:

Shift: IIf(CDate(([DateTimeInput]-7/24)-Int([DateTimeInput]-7/24))>=TimeSerial(16,0,0),IIf(Weekday(CDate([DateTimeInput]-7/24))>4,"BHN",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHN","FHN/BHN")),IIf(CDate(([DateTimeInput]-7/24)-Int([DateTimeInput]-7/24))<TimeSerial(8,0,0),IIf(Weekday(CDate([DateTimeInput]-7/24))>4,"BHD",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHD","FHD/BHD")),IIf(Weekday(CDate([DateTimeInput]-7/24))>4,"BHM",IIf(Weekday(CDate([DateTimeInput]-7/24))<4,"FHM","FHM/BHM"))))

In words this basically says, if the time is later than 16 hours after the start of Day shift and the day of the week is after Wednesday, it's BHN shift, else if its before Wednesday, then its FHN shift, otherwise it's Wednesday and therefor both shifts were working. Then it repeats this for if the DateTime is less than 8 hours after the start of Day Shift, checks the day of the week and assigns FHD, FHM or FHN, or both, then repeats one more time for Mid shift.

What I am hoping is there is something better or easier to accomplish this?

Hi Kerry, your formula is a pretty ingenious creation for what it is.  If you want to simplify it, you need to simplify the underlying methodology.  I think you need to add one key info: the date and time of the start of the first shift of the week, FHD.  

Based on the info you have posted, day shift is 7am to 3pm, mid-shift is 3-11pm, and night shift is 11pm-7am.  And I'm assuming "front" is Mon to Wed, and "Back" is Wed to Fri.

Let's use the current week as an example.  The start of this week's FHD is (was) Monday 10/10/22 7am.  Let's call this date value d.

Then:

FHD is between d and d + 8 hours, OR between d + 24 hrs and d + 32 hours.
FHM is between d + 8 and d + 16 hours, OR between d + 32 hrs and d + 40 hours.
FHN is between d + 16 and d + 24 hours, OR between d + 40 hrs and d + 48 hours.
FHD/BHD is between d + 48 hrs and d + 56 hours.
FHM/BHM is between d + 56 hrs and d + 64 hours.
FHN/BHN is between d + 64 hrs and d + 72 hours.
    
And so on for BHD, BHM, and BHN.  You get the picture.  That makes the ensuing formula much simpler.

Also, as you have figured out, the Access date value of N hours equals N/24 (i.e. Access stores 8 hours as 8/24, or 0.3333).

If a given date and time is C, then you can find out what shift C belongs to with the Switch() function, which Richard has made a video about.  In short, Switch() checks for a series of conditions from left to right, and returns the proper value:

=Switch(cond1, value if cond1 is true, cond2, value if cond2 is true, ... ... , value if all previous conditions are false)

So your formula is:

=Switch(C >= d AND C < d+1/24, "FHD", C >= d=24/24 AND C < d + 32/24, "FHD", C >= d+1/24 AND C < d+16/24, "FHM", C >= d+32/24 AND C < d+40/24, "FHM", C >= d+16/24 AND C < d+24/24, "FHN", C >= d+40/24 AND C < d+48/24, "FHN", ... ... , "")

It still looks formidable, but at least the syntax is easier to follow.

Thank you! I never thought of a Switch function and I will go look up what Richard has taught about it.

In honor of Rick's latest "fast tips" video on TimeValue, I thought I would clean up my formula above. Again, I had to come up with a Calculated field for [Shift] where the work week is divided up into 6 total crews - 3 of them Sunday through Wednesday and the other 3 Wednesday through Saturday. Both crews work simultaneously on Wednesday. The shift names are abbreviations for "Front Half" or "Back Half" Day, Mids, and Nights. The whole formula is below if you want to copy and paste it to try yourself, or modify it for your own uses.

Shift:
IIf(TimeValue([DateTimeInput]-7/24)>=TimeSerial(16,0,0),    //Checks to see if night shift
    IIf(Weekday([DateTimeInput]-7/24)>4,                             //If Night Shift, checks to see if after Wed
        "BHN",                                                                       //Is Night Shift, and after Wednesday
        IIf(Weekday([DateTimeInput]-7/24)<4,                         //Is Night Shift, checks to see if before Wed
            "FHN",                                                                   //Is Night Shift, and before Wednesday
            "FHN/BHN")),                                                         //Is Night Shift on Wednesday
    IIf(TimeValue([DateTimeInput]-7/24)<TimeSerial(8,0,0),    //Is Not Night, checks to see if Day Shift
        IIf(Weekday([DateTimeInput]-7/24)>4,                         //Is Day shift, checks to see if after Wed
            "BHD",                                                                   //Is Day shift after Wednesday
            IIf(Weekday([DateTimeInput]-7/24)<4,                     //Is Day Shift, checks to see if before Wed
                "FHD",                                                               //Is Day Shift before Wednesday
                "FHD/BHD")),                                                     //Is Day shift on Wednesday
        IIf(Weekday([DateTimeInput]-7/24)>4,                         //Is not Day or Night, checks if after Wed
            "BHM",                                                                   //Is not Day or Night, but after Wednesday
            IIf(Weekday([DateTimeInput]-7/24)<4,                     //Is Not Day, Night, checks if before Wed
                "FHM",                                                               //Is not Day or night but before Wed
                "FHM/BHM"))))                                                   //Is not Day, night, but is Wednesday

Whole formula:

Shift: IIf(TimeValue([DateTimeInput]-7/24)>=TimeSerial(16,0,0),IIf(Weekday([DateTimeInput]-7/24)>4,"BHN",IIf(Weekday([DateTimeInput]-7/24)<4,"FHN","FHN/BHN")),IIf(TimeValue([DateTimeInput]-7/24)<TimeSerial(8,0,0),IIf(Weekday([DateTimeInput]-7/24)>4,"BHD",IIf(Weekday([DateTimeInput]-7/24)<4,"FHD","FHD/BHD")),IIf(Weekday([DateTimeInput]-7/24)>4,"BHM",IIf(Weekday([DateTimeInput]-7/24)<4,"FHM","FHM/BHM"))))