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
 
Sort by date Sort by votes

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
:mad: :mad:
NOT Round BUT Trunc:
Excel Formula: 
=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))

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 3     
7Mon01-magActivity 409:0017:00         Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4  
8Mon01-magActivity 510:0018:00          Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5 
9Mon01-magNew 110:0016:00          New 1New 1New 1New 1New 1New 1   
10Mon01-magNew 210:0018:00          New 2New 2New 2New 2New 2New 2New 2New 2 
11
Foglio3
Cell Formulas
RangeFormula
F4:X10F4=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))
 
Upvote 0
Solution
The only one that is appearing correctly is Activity 1: Start/finish time says 06:00 - 16:00. The result starts populating at 06:00, for 10 hours finishing at 16:00.

The rest are all appearing wrongly:

Activity 2 - Start time says 07:00 but the result starts at 08:00, for 10 hours finishing at 18:00
Activity 3 - Start/finish time says 08:00 - 14:00 but the result starts at 08:00, for 8 hours finishing at 15:00
Activity 4 - Start/finish time says 09:00 - 17:00 but the result starts at 09:00, for 9 hours finishing at 18:00
Activity 5 - Start/finish time says 10:00 - 18:00 but the result starts at 11:00, for 7 hours finishing at 18:00
New 1 - Start/finish time says 10:00 - 16:00 but the result starts at 11, for 5 hours finishing at 16:00
New 2 - Start/finish time says 10:00 - 18:00 but the result starts at 11, for 7 hours finishing at 18:00
 
Upvote 0
eraust said:
... 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.
Click to expand...
See if the following formula works for you:
Excel Formula: 
=IF($D4="","",(IF(AND(MROUND(F$1,"0:01")>=MROUND($D4,"0:01"),MROUND(F$1,"0:01")<MROUND($E4,"0:01")),$C4,"")))
 
Upvote 0
Anthony47 said:
:mad: :mad:
NOT Round BUT Trunc:
Excel Formula: 
=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))

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 3     
7Mon01-magActivity 409:0017:00         Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4  
8Mon01-magActivity 510:0018:00          Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5 
9Mon01-magNew 110:0016:00          New 1New 1New 1New 1New 1New 1   
10Mon01-magNew 210:0018:00          New 2New 2New 2New 2New 2New 2New 2New 2 
11
Foglio3
Cell Formulas
RangeFormula
F4:X10F4=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))
Click to expand...
Aha! This looks like it's working!
 
Upvote 0
Anthony47 said:
:mad: :mad:
NOT Round BUT Trunc:
Excel Formula: 
=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))

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 3     
7Mon01-magActivity 409:0017:00         Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4Activity 4  
8Mon01-magActivity 510:0018:00          Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5Activity 5 
9Mon01-magNew 110:0016:00          New 1New 1New 1New 1New 1New 1   
10Mon01-magNew 210:0018:00          New 2New 2New 2New 2New 2New 2New 2New 2 
11
Foglio3
Cell Formulas
RangeFormula
F4:X10F4=IF($D4="","",(IF(AND(F$1>=TRUNC($D4,10),F$1<TRUNC($E4,10)),$C4,"")))
Click to expand...
This has worked perfectly, thank you so much.

I'm not sure I understand why it's working? Any chance you could explain?
 
Upvote 0
Excel represents Hours as a decimal number ranging from 0.0 (0h0m0s) to 1 (24h00m00s)
Cartel5
EF
17HH:MMDecimal
1801:00 0,0416666666666667
1902:00 0,0833333333333333
2003:00 0,1250000000000000
2104:00 0,1666666666666670
2205:00 0,2083333333333330
2306:00 0,2500000000000000
2407:00 0,2916666666666670
2508:00 0,3333333333333330
2609:00 0,3750000000000000
2710:00 0,4166666666666670
2811:00 0,4583333333333330
2912:00 0,5000000000000000
3013:00 0,5416666666666670
3114:00 0,5833333333333330
3215:00 0,6250000000000000
3316:00 0,6666666666666670
3417:00 0,7083333333333330
3518:00 0,7500000000000000
3619:00 0,7916666666666670
3720:00 0,8333333333333330
3821:00 0,8750000000000000
3922:00 0,9166666666666670
4023:00 0,9583333333333330
4100:00 1,0000000000000000
Foglio3
Cell Formulas
RangeFormula
F18:F41F18=E18
E19:E41E19=D19/24


Wherever the decimal value is represented by 15 significant figures (the maximum significant figures that excel uses) there is the risk that a "calculated hour" differs from the same typed hour by a "flick". Truncating decimal to one of the terms of a comparison helps avoiding that "a flick" make the comparison failing
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Dynamically extend list help
Replies
3
Views
91
Cubist
Cubist
J
  • Solved
Macro to Copy Cell Adjacent in One Sheet and Paste in Another Sheet
Replies
1
Views
449
mumps
mumps
F
  • Question Question
cut defined cells from a sheet paste them on another sheet then delete incompletes rows
Replies
2
Views
732
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
839
Akuini
Akuini
T
  • Question Question
When copy formula nothing happens.
Replies
2
Views
569
Tofik
T

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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