My Online Training Hub

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

You are here: Home / Excel add hours to date|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_PrintTopic sp_TopicIcon
Excel add hours to date
Avatar
yi lu

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
1
November 6, 2019 - 10:58 am
sp_Permalink sp_Print

hello, please use your talent to help solve the issue below. Thank you very much!

Excel add hours to date excluding weekends and holidays. start at 09:30 on the first working day every week, finish if less than 17 hours before 07:00 on Saturday. 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
November 6, 2019 - 2:59 pm
sp_Permalink sp_Print

Can you provide some examples?

"finish if less than 17 hours before 07:00 on Saturday" sounds strange, will be easier to understand you problem if you provide sample data with expected results.

sp_AnswersTopicAnswer
Answers Post
Avatar
yi lu

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
3
November 7, 2019 - 4:13 am
sp_Permalink sp_Print

Thanks Catalin!

there is the example.

A (date and time) B (hours between 2 time)
04/11/2019 09:30 (first day every week always 09:30, this example is November 04.) 17
05/11/2019 02:30 (given a date and time every 17 hours) 17
05/11/2019 19:30 (ditto) 17
06/11/2019 12:30 (ditto) 17
07/11/2019 05:30 (ditto) 17
07/11/2019 22:30 (ditto) 17
08/11/2019 15:30 ( It is 15.5 hours from Friday 15:30 to Saturday 07:00, less than 17 hours, then start 09:30 next work day.)  17
12/11/2019 09:30 (first day this week is Tuesday as Nov/11 is holiday.) 17
13/11/2019 02:30 ........................ 17
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
November 7, 2019 - 4:30 am
sp_Permalink sp_Print

And where is the expected result?

Avatar
yi lu

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
5
November 7, 2019 - 4:48 am
sp_Permalink sp_Print

date and time are the result. make a formula to calculate next date and time.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
November 7, 2019 - 5:48 am
sp_Permalink sp_Print

It's obvious that you need a formula for that. I was talking about manual results, so we can see some example calculation results.

Avatar
yi lu

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
November 6, 2019
sp_UserOfflineSmall Offline
7
November 7, 2019 - 10:11 am
sp_Permalink sp_Print

Thank you.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy, Dieneba NDIAYE, Ben Hughes, Jay Soysa, Marco Marques, Christopher Anderson, Natasha Smith
Guest(s) 8
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:
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Kelly smith
Alan Rushton
Zack Nilsson
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27181

 

Member Stats:
Guest Posters: 49
Members: 31858
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen