Help with issues calculating using dates

E

eraust

New Member
Joined
Sep 13, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi All

I'm attempting to make a schedule which autopopulates/calculates based on a start/finish time.

I've asked for help previously with different versions of this schedule but this particular version, I'm having an issue with the activities that lasts 10 hours.

Basically, if the start time is 04:00, 07:00, 10:00 or 13:00 - it's populating 11 hours rather than 10. This isn't happening with the activities that last 6 or 8 hours or any other start times other than those listed. I've tried checking the format of all the elements that are involved in my formula and everything seems to be the same (custom, hh:mm) so I can't figure out what's going wrong.

I'm sure there are different ways of making this work but I'd rather keep the formulas I'm using the same if possible.

Thanks in advance all

SampleSchedule.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1Start timeEnd time00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:0000:0001:0002:0003:0004:0005:0006:00
4Mon01/05Activity 106:0016:00      Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1               
5Mon01/05Activity 207:0017:00       Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2             
6Mon01/05Activity 308:0014:00        Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3                 
7Mon01/05Activity 409:0017:00         Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4              
8Mon01/05Activity 510:0018:00          Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5             
Schedule
Cell Formulas
RangeFormula
A4:A8A4=TEXT($B$4,"ddd")
B4:B8B4=$A$2
E4:E8E4=IF(D4<>"",_xlfn.XLOOKUP(C4,Sheet2!$A$2:$A$6,Sheet2!$B$2:$B$6)+D4,"")
AE4:AK8,F4:AC8F4=IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
Cells with Data Validation
CellAllowCriteria
D4:D8List=Sheet2!$A$10:$A$33
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
I think something is going on in Sheet 2 where you lookup the duration of the activities. I suspect you have a rounding error. Look at this:

MrExcelPlayground17.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Start timeEnd time12:00:00 AM1:00:00 AM2:00:00 AM3:00:00 AM4:00:00 AM5:00:00 AM6:00:00 AM7:00:00 AM8:00:00 AM9:00:00 AM10:00:00 AM11:00:00 AM12:00:00 PM1:00:00 PM2:00:00 PM3:00:00 PM4:00:00 PM5:00:00 PM6:00:00 PM
2
3
4Sat0Activity 1                   
5Sat0Activity 27:0017:00       Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2  
6Sat0Activity 37:0017:00       Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3 
7Sat0Activity 4                   
8Sat0Activity 5                   
9
10
110.4166666666667
12
130.4166670000000
Sheet20
Cell Formulas
RangeFormula
A4:A8A4=TEXT($B$4,"ddd")
B4:B8B4=$A$2
F4:X8F4=IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
E5E5=D5+E11
E6E6=D6+E13
 
Upvote 0
Ok I see what you mean.

I've checked the sheet and they all seem to be the same. I'm also not sure if that would explain why it's only some start times that are off? If I put the start time down as 06:00 or 21:00 for example, it displays 10 hours.
 
Upvote 0
When you work with timings you often need to use ROUND; so my proposal: in F4
Excel Formula: 
=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))
 
Upvote 0
Anthony47 said:
When you work with timings you often need to use ROUND; so my proposal: in F4
Excel Formula: 
=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))
Click to expand...
Is there a way to apply this to my Sheet 2, which is where I'm getting my durations from rather than adding the 10 into the formula?

Right now this sheet has 5 activities on it, but the real version has over 50 with all different durations. So I have a Sheet 2 which is essentially the name of the activity along with the duration in hh:mm. Column E looks up the duration of each activity in Sheet 2 and calculates based on the start time.
 
Upvote 0
I have been working on Sheet20, the formula in F4 that then has to be copied to the right and down;
not =IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
but the one with ROUND
 
Upvote 0
You might cheet, by subtracting a small amount from the end time:

MrExcelPlayground17.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Start timeEnd time12:00:00 AM1:00:00 AM2:00:00 AM3:00:00 AM4:00:00 AM5:00:00 AM6:00:00 AM7:00:00 AM8:00:00 AM9:00:00 AM10:00:00 AM11:00:00 AM12:00:00 PM1:00:00 PM2:00:00 PM3:00:00 PM4:00:00 PM5:00:00 PM6:00:00 PM
2
3
4Sat0Activity 1
5Sat0Activity 27:0017:00Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2  
6Sat0Activity 37:0017:00Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3  
7Sat0Activity 4
8Sat0Activity 5
9
10
110.4166666666667
12
130.4166670000000
Sheet20
Cell Formulas
RangeFormula
A4:A8A4=TEXT($B$4,"ddd")
B4:B8B4=$A$2
M5:W6M5=IF($D5="","",(IF(AND(M$1>=$D5,M$1<$E5-0.0001),$C5,"")))
X5:X6X5=IF($D5="","",(IF(AND(X$1>=$D5,X$1<$E5+0.0001),$C5,"")))
E5E5=D5+E11
E6E6=D6+E13
 
Upvote 0
You might cheet, by subtracting a small amount from the end time
Click to expand...
Round do that job
And theoretically there is a problem even with the Start Time
 
Upvote 0
Anthony47 said:
I have been working on Sheet20, the formula in F4 that then has to be copied to the right and down;
not =IF($D4="","",(IF(AND(F$1>=$D4,F$1<$E4),$C4,"")))
but the one with ROUND
Click to expand...
So I applied this and it works on the activities that last 10 hours. However, my activities that last 6 and 8 are now out of sync with the issue that I was having with the 10 hour ones.

6 hour durations are now displaying as 7 hours and 8 as 9.
 
Upvote 0
Using your data plus two new activities I get the following, that seems to be correct:

Cartel5
ABCDEFGHIJKLMNOPQRSTUVWX
1Start timeEnd time00:0001:0002:0003:0004:0005:0006:0007:0008:0009:0010:0011:0012:0013:0014:0015:0016:0017:0018:00
2
3
4Mon01-magActivity 106:0016:00      Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1Activity 1   
5Mon01-magActivity 207:0017:00        Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2Activity 2 
6Mon01-magActivity 308:0014:00        Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3Activity 3    
7Mon01-magActivity 409:0017:00         Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4 
8Mon01-magActivity 510:0018:00           Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5 
9Mon01-magNew 110:0016:00           New 1New 1New 1New 1New 1   
10Mon01-magNew 210:0018:00           New 2New 2New 2New 2New 2New 2New 2 
11
Foglio3
Cell Formulas
RangeFormula
F4:X10F4=IF($D4="","",(IF(AND(F$1>=ROUND($D4,10),ROUND(F$1,10)<$E4),$C4,"")))


Is it wrong?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro to Copy Cell Adjacent in One Sheet and Paste in Another Sheet
Replies
1
Views
444
mumps
mumps
F
  • Question Question
cut defined cells from a sheet paste them on another sheet then delete incompletes rows
Replies
2
Views
725
Flavien
F
G
  • Solved
Sort/filter formula used for search formula combobox issue with shorter named values, Formula help for search combobox
Replies
6
Views
832
Akuini
Akuini
T
  • Question Question
When copy formula nothing happens.
Replies
2
Views
566
Tofik
T
M
  • Solved
VBA Code to split week ranges into separate lines.
2
Replies
10
Views
2K
Joe4
Joe4

Forum statistics

Threads
1,223,885
Messages
6,175,186
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top