• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Work Time Calculation|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Work Time Calculation|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Work Time Calculation
sp_PrintTopic sp_TopicIcon
Work Time Calculation
Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
1
January 20, 2021 - 8:46 pm
sp_Permalink sp_Print

Work time is Monday to Friday 9am to 5pm

In column A I have a list of dates/times to which i need to add 6 hours. If adding 6 hours takes me beyond 5 pm then the additional hours should be added to the next day starting at 9am

Example

Column A             Column B
20/01/2021 13:45 20/01/2021 19:45

Adding 6 hours takes me beyond 5pm so I need a formula that would return 21/01/2021 11:45

In addition if the example was on a Friday it should add the additional hours on from Monday as in the example below

Column A             Column B
22/01/2021 13:45 25/01/2021 11:45

 

Thanks for any help

 

Paul

Avatar
Lionel Baijot
Member
Members
Level 0
Forum Posts: 114
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
2
January 21, 2021 - 3:47 pm
sp_Permalink sp_Print

Hi Paul,

Here is a solution with a formula. 

BR,

Lionel

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
3
January 22, 2021 - 12:03 am
sp_Permalink sp_Print

Hi Lionel

That is great thank you

Is it possible to adapt that formula so that if the start time is out of work hours (between 17:01 & 08:59) the 6 hours is added on from 09:00, so always giving the answer 15:00

Thanks in advance for any help

Paul 

Avatar
Lionel Baijot
Member
Members
Level 0
Forum Posts: 114
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
4
January 22, 2021 - 2:53 am
sp_Permalink sp_Print

Paul,

A new version.

BR,

Lionel

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
5
January 22, 2021 - 7:08 pm
sp_Permalink sp_Print

Hi Lionel

Thank you that is perfect

I struggle with time calculations in excel, so have been looking at your formula and whilst I could not have found that solution, I have looked at it and mostly understand what it is doing. Which I find useful as I may have another application in the future I can adapt this for

 

I have one question, as I do not understand why you have used 360/60/24 and not just 6/24 to get the 6 hour gain?

 

Thanks again

Paul

Avatar
Lionel Baijot
Member
Members
Level 0
Forum Posts: 114
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
6
January 23, 2021 - 1:11 am
sp_Permalink sp_Print

Paul,

For the 360/60/24, there is no reason. It's just that at certain times the brain does certain things automatically.
You are absolutely right that we can also use 6/24.

BR,

Lionel

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
7
January 23, 2021 - 5:03 am
sp_Permalink sp_Print

Ok thanks Lionel, just checking there wasn’t a reason for your doing that way. 
Thanks again for the solution, it will save many hours of work every month and improve accuracy

 

Paul

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
8
January 25, 2021 - 8:11 pm
sp_Permalink sp_Print sp_EditHistory

Hi Lionel

I have just put the formula in my spreadsheet, back to the start of the year and found a problem with entries that are on a Saturday and Sunday, which all should return an answer which is Monday 15:00

Like Friday it should add the additional hours on from Monday as in the example below

Column A             Column B
16/01/2021 13:45 18/01/2021 15:00

17/01/2021 11:15  18/01/2021 15:00

All entries between 17:00 Friday and 08:00 Monday should return an answer which is Monday 15:00

I have added a second sheet with the problems highlighted yellow in your spreadsheet attached

 

Is it possible to fix this?

 

Thanks

 

Paul

Avatar
Lionel Baijot
Member
Members
Level 0
Forum Posts: 114
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
9
January 26, 2021 - 1:11 am
sp_Permalink sp_Print

Hi Paul

Here is the adaptation for the WE. I added a column E (in green) with the new formula.

BR,

Lionel

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
10
January 28, 2021 - 1:01 am
sp_Permalink sp_Print sp_EditHistory

Hi Lionel

Once again many thanks for your help

I have used your new formula with my data, back to the beginning of the year, it was all ok until I realised that Friday 1st January was a holiday and thus not a work day

I researched the WORKDAY.INTL function in the formula and found I could add the holiday dates into the formula, which I did but did not get the expected result

On sheet 2 of the attached spreadsheet the formula I used is in column G (blue) and the holidays listed in column J. The results that I think are wrong are highlighted yellow with the expected results next to them in column H

I think all entries between 31/12/2021 17:00 and 04/01/2021 09:00 should return an answer which is 04/01/2021 15:00

I have added a third sheet covering 01/04 to 08/04 to see what the results looked like over the weekend as both Friday 02/04 and Monday 05/04 are holidays, again there were unexpected results. The results that I think are wrong are highlighted yellow with the expected results next to them in column H

Is it possible to fix this

Also on your formula below, if the target was changed from 6 hours to:

5 hours, would I just need to change 60 to 72 and 15/24 to 14/24

4 hours, would I just need to change 60 to 90 and 15/24 to 13/24

=IF(WEEKDAY(B2,2)>5,WORKDAY.INTL((INT(B2)+17/24),1,1)+15/24,IF(B2<(INT(B2)+9/24),(INT(B2)+15/24),IF(B2>(INT(B2)+17/24),WORKDAY.INTL((INT(B2)+17/24),1,1)+15/24,IF((B2+360/60/24)>(INT(B2)+17/24),WORKDAY.INTL((INT(B2)+17/24),1,1)+9/24+MOD((B2+360/60/24)-(INT(B2)+17/24),1),B2+360/60/24))))

Thanks

Paul

Avatar
Lionel Baijot
Member
Members
Level 0
Forum Posts: 114
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
11
January 30, 2021 - 12:21 am
sp_Permalink sp_Print

Hi Paul,

Here is an adaptation of the file taking into account the public holidays (which are on D2:D9). I have added an M column with the new results (which seem to be correct) on a new sheet (4).

You will see, the formula has been modified very slightly. This concerns the first condition where I check that the date is either a weekend day or a public holiday.

Still a little closer to perfection 😉

BR,

Lionel

Avatar
Paul Sheppard
Member
Members
Level 0
Forum Posts: 7
Member Since:
January 20, 2021
sp_UserOfflineSmall Offline
12
February 2, 2021 - 1:30 am
sp_Permalink sp_Print

Hi Lionel

Thanks for your patience in sorting this out for me

I have put your latest formula into my spreadsheet to the beginning of the year and everything looks good and hopefully will continue to

 

Thanks again

 

Paul

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, David, Dario Serrati, yonatan zelig, Christopher Anderson
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.