SOLVED

Time Frame

Occasional Contributor

Anyone know how to set up a Begin & End time using the Actual Hours added to the date to get the End date and time in Excel? Per the Example- Formulas and Functions

Actual HrsBeginEnd
4.886/30/20227/1/2022
2.01  
5.92  
7.22  
3.91  
12.69  
2.23  
9.25  
2.23  

Is this a possibility in Excel?

27 Replies
My Begin Date & Time with End Date & Time Columns looks like this-
6/30/2022 19:28 7/1/2022 10:21
So, my next column date and time should add H3 to J2(time) for a new begin date and time and so on.
best response confirmed by KennySr (Occasional Contributor)
Solution

@KennySr 

In I3:

=J2+H3/24

Thanks Hans for replying- my overall achievement is to get a schedule of completion with the hours for a time frame. Can this be done?
It gives a date of completion with the formula given but does not display the time of completion.
If it isn't possible- I can work with this formula. Thanks again Hans.

@KennySr 

Make sure that you format columns I and J as date AND time, for example using the custom number format m/d/yyyy h:mm AM/PM

Thanks Hans.
I have to do some manual manipulation with the schedule format as well to exclude Sundays and holidays. Excel doesn't recognize these hours being excluded.

@KennySr 

It's hard to visualize what you mean. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

How do I send you a sample workbook? This would be my first one being sent. Let me know- all sensitive material has been excluded.

@KennySr 

If this option

image.png

when you reply on post is not available for you, share on any resource as @Hans Vogelaar suggested and share the link. Be sure access is granted.

Thanks Sergei- I do not have those drag and drop options displayed on my end. Will have to do as what Hans and you have suggested. Much appreciated for your help.

@KennySr 

I have received your workbook in your PM.

This is what I see in columns G to I:

S1491.png

What exactly do you want to do?

I believe that the Excel spreadsheet does not have the capability to achieve what I need.
I am trying to set up a "real time" schedule based on the hours in which we work. No weekends, no holidays and only 16 hour days. The formula that you gave me worked but represents that we are working 24 hours a day 7 days a week. Unless I am missing something in the background that could allow what we are looking for.

@Kenny_Sr 

You mention 16-hour working day - from when to when?

6am-10pm

@Kenny_Sr 

Your sample workbook has several rows starting on weekend days and on public holidays. What's up with that?

Exactly what I was trying to avoid. The formula that was inputted was divided by 24 which I think maybe there might be something that we should look at in the background to see if we could adjust to eliminate weekends and holidays and to only have 16 hour days scheduled.

@Kenny_Sr 

See the attached version. It is now a macro-enabled workbook, so you'll have to allow macros.

Looks awesome on my end- much appreciated- will try it out on another scheduled machine and let you know how it turns out.
Is the sheet 1 that is labeled Holidays at the top pulling that information into the formula?