End Date INCLUDING Time Calculation

[HeLzWright]

Howdy all,

I couldn't find the answer over the forums I've read so far so I figured I'd ask the question directly.

I'm looking to calculate the end date INCLUDING time. Most of the worksheet is extremely basic. Hopefully this helps. To clarify on workdays and weekends and hours. We are working 24 hours starting at 6:00 AM, Monday-Thursday and ending at 6:30 AM, Friday (96 total hours) with "weekends" being Friday-Sun. The "=NOW()" Function works for the start date (L2) because I would like to look at it at any time and it would be accurate. I can enter a something manually in L2 if I needed to do a future project.

Everything I have so far works exactly as I would like (A lot of it is me playing around) except the end date due to the fact it's technically showing 00:00 (12AM) for every day AKA no decimal. I'd like it to update using the "=NOW" function in L2. I know they are not in date format. I was doing this to confirm the decimal (time) was showing up before attempting to correct format and not know why I'm showing a #Value, #####, or whatever. Bear in mind I'm learning as I go. My skills are less than admirable. I've noticed some of the responses are... complicated, in my experience, and take some time to decipher.

1. I'm almost positive my "=WORKDAY.INTL()" function doesn't really work as planned since we go into Friday technically.
2. I've tried a few things to get the time(decimal) on the workday function but nothing has been successful. By tried, I mean copy, paste, and use the correct variables because I'm not that great at excel.
3. I hide a lot of this table to clean up the look, so adding more lines is not an issue.
4. Lines 2 & 3 are there for auto-fill

I feel like I'm missing some needed information but I'm not sure. Thanks in advance. I'm assuming this is an elaborate calculation for a simple problem type deal.

[lazyme]

Use the TIME() function.

So your K4 formula should be:

Please Login or Register  to view this content.

[HeLzWright]

Unfortunately it doesn't properly change the date and it's using the "weekends" as well if I try to have it use the same "type" of calculations for the rest of the document...

[lazyme]

Yeah, sorry, that was completely rubbish.

Give me a few minutes

[HeLzWright]

After I spend allllll this time trying other things! haha That's probably a tad simpler than I did. I've attached it.

I've changed quite a few things... more than I can truly remember. I haven't had a chance to test mine out, but I will test your's ASAP as well.

I've hidden a lot of stuff again to clean it up visually.

[lazyme]

Please Login or Register  to view this content.

Formula:
IF([# of Workable Time Left This Week]<[Total Work Time],

[Monday Next Day]+([Total Work Time]-[Last Week's Workable Time],

[Today]+[Total Work Time]

)

[# of Workable Time Left This Week] -

Please Login or Register  to view this content.

[Total Work Time] -

Please Login or Register  to view this content.

[Monday Next Week] -

Please Login or Register  to view this content.

[lazyme]

I might be misunderstanding your criteria but I am checking your new sheet and I don't think the end date is correct.

For example:
Now = 03/29 3:19PM
Total Work = 57.73hrs
No Work this week as shift ended at 6:30am today.
So 04/01 6AM + 57.73 hrs = 04/03 12.44PM
But your worksheet is saying 04/02 10:03PM

[HeLzWright]

I added 2 hours of "downtime" or non work hours at the end of the week. So i guess to simplify; Work week end Friday at 4:30 AM. I didn't account for this originally.

There is also downtime between each material but it's one of three things. If the grade changes 18000 sec. If the grade is the same 7000 sec. No time for no material AKA end of job.

I see that my sheet has an error. I apologize. I'm at work so I'm having to go between the two and I got in a hurry.

I have a minute now to implement your stuff!

[HeLzWright]

So i think I got it... I used your Idea to an extent.

I created two days. One to check if the end time is within parameters of the work week and a second as a result of the first date.

Whereas (E4)Monday, 6:00 AM = 2.25 and (F4)Friday, 4:30 AM = 6.1875

To Test First date if it falls "outside" the work week or past Fri, 4:30AM.

Please Login or Register  to view this content.

To calculate the amount of hours remain and add to Mon, 6:00AM.

Please Login or Register  to view this content.

If the finish date is less than end of work week or "less" than Mon, 6:00AM. If both are FALSE, use the original date.

Please Login or Register  to view this content.

Like I said I think I figured it out. Had to modify what you had used a "little". I understood what you were trying to do, though it took me a minute to figure it out.