Formulas, Functions, Formatting, Charts, Data Analysis, etc.
by Sonja » Thu Jan 28, 2016 4:41 am
How can I calculate numbers of hours that fall into day and night working hour.
Some time I work for shift that atarts before 6 am (6:00) and end during regular time. Night working time start at 10pm (22:00) and end at 6 am (6:00)
For total working hours I use this formula for cell D2
=(D2-C2+(C2>D2))*24
For day hours I use this for cell E2
=IF(AND(C2>0;D2>0);IF(D2<=C2;D2+"24:00";D2)-C2-MAX(MIN("6:00";IF(D2<=C2;D2+"24:00";D2))-C2;0)-MAX(MIN("30:00";IF(D2<=C2;#REF!+"24:00";D2))-MAX("22:00";C2);0);"")*24
For night hours (for F4) just substract
=D4-E4
Everything work fine when I emter time but when a cell is empty or when I enter text I have a problem.
A B C D E F
1 FROM TO TOTAL DAY NIGHT
2 05:00 15:00 10 9 1
3 #VALUE! #VALUE! #VALUE!
4 OFF OFF #VALUE! #VALUE! #VALUe!
I wuld be most grateful if someone could give me a solution for this problem
Thank you
-
Sonja
- Rookie
-
- Posts: 2
- Joined: Jan 28, 2016
- Reputation: 0
- Excel Version: 2003
by pecoflyer » Thu Jan 28, 2016 6:17 am
Hi and welcome
please post a sample sheet ( no pics please). Thx
A relevant topic title helps get faster and more answers
-
pecoflyer
- Moderator
-
- Posts: 1274
- Joined: Jan 24, 2012
- Location: Belgium
- Reputation: 39
- Excel Version: 2003/2007/2010
by Sonja » Fri Jan 29, 2016 4:29 am
Here's my sample.
Thanks
You do not have the required permissions to view the files attached to this post.
-
Sonja
- Rookie
-
- Posts: 2
- Joined: Jan 28, 2016
- Reputation: 0
- Excel Version: 2003
by Beamer » Fri Jan 29, 2016 6:31 am
Will the iserror function do the job?
in Cell D2: =IF(ISERROR(C2-B2+(B2>C2))*24,0,(C2-B2+(B2>C2))*24)
-
Beamer
- Excel Junkie
-
- Posts: 217
- Joined: Oct 3, 2014
- Location: New South Wales, Australia
- Reputation: 31
- Excel Version: 2010
by Kevin UK » Fri Jan 29, 2016 6:45 am
Hi Sonja
See the attached, shorter formulas, if you had Excel 2007> you could shorten them using the IFERROR function.
You do not have the required permissions to view the files attached to this post.
Regards
Kevin
-
Kevin UK
- Excel Master
-
- Posts: 568
- Joined: Jun 24, 2012
- Reputation: 86
- Excel Version: 2016
Return to General Excel Questions
Who is online
Users browsing this forum: No registered users and 124 guests