Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
need help with calculating difference between two dates timestamps which should exclude weekends.
eg: date1= 12-nov-2018 8:30 am
date2 = 19-nov-2018 9:30 am
need the difference(date2-date1) result = 5 days 1hr
Hi Prithvi,
Use interval function:
Thanks,
Vitalii
Hi Prithvi, you can use Networkdays to count working days:
In example:
=If(date1_time>date2_time,
(NetWorkDays(date1_date,date2_date)-1) & 'days ' & time(date2_time-date1_time, 'h') & 'hr',
(NetWorkDays(date1_date,date2_date)-2) & 'days ' & time(date2_time-date1_time, 'h') & 'hr')
Ruben, Eager to know. Her requirement is little different. I am trying to help but not yet solved. May be you have other work around. I can think Master Calendar with Timestamp for every hour.
Coming to requirement.
Input:
Load * Inline [
date1, date2
12-nov-2018 8:30 am, 19-nov-2018 9:30 am
];
She need result as below
date1, date2, Expected
12-nov-2018 8:30 am, 19-nov-2018 9:30 am, 5 Days 1 Hour
Explanation about requirement
date1 start @8:30 am that means she need to calculate as
12-nov-2018 8:30 am to 13-nov-2018 8:30 am as 1 Day
13-nov-2018 8:30 am to 14-nov-2018 8:30 am as 2 Day
14-nov-2018 8:30 am to 15-nov-2018 8:30 am as 3 Day
15-nov-2018 8:30 am to 16-nov-2018 8:30 am as 4 Day
16-nov-2018 8:30 am to 17-nov-2018 8:30 am as 5 Day
17-nov-2018 8:30 am to 18-nov-2018 8:30 am as 6 Day
18-nov-2018 8:30 am to 19-nov-2018 8:30 am as 7 Day
Till here We got it as 7 Days - And in this arbitrary she want to exclude 2 Days as in this week we have Saturday and Sunday.
That means we will get 5 Days. Now How 1 Hour is needed?
From 19-nov-2018 8:30 am to 19-nov-2018 9:30 am --> If we calculate, It is showing 1 Hour.
So, Finally Result field is 5 Days 1 Hour
I hope I make clear
Hi Anil, sorry but I don't understand the issue, I tried to set fixed values to make a test with '12-nov-2018 8:30 am' as date1 and '19-nov-2018 9:30 am' as date2:
=If(time('9:30')>Time('8:30'),
(NetWorkDays('12/11/2018','19/11/2018')-1) & 'days ' & time('9:30'-'8:30', 'h') & 'hr',
(NetWorkDays('12/11/2018','19/11/2018')-2) & 'days ' & time('8:30'-'9:30', 'h') & 'hr')
This returns '5days 1hr', is this wrong?
That means, you are splitting Date and Time different - If i understand correct. But when we are doing with full timestamp it is making problem. I hope this resolve for priqvqlik
Yes, I separated them, that causes to have a check to know with time part is higher to get the correct days, but allows to use networkdays() or a flag in dates of master calendar to count working days and also helps keeping the time part as a simple substraction.
Maybe other way can be using (Date1-Date2)-Count(WeekendDatesInInterval)... still you will have to deal to give in 'x days h hours' format, wich will be easier having separated days and hours.
Thanks Ruben!
this expression works but is not giving results accurately for some of the date values.
eg:
date1 & time1= 20-Aug-2016, 12:32 am
date2 & time2 = 20-Aug-2016, 1:42 am
difference of these would be 1hr 10min, this can can be rounded to 2hr
but the expression is returning as -1day 1hr.
can you please help with this , also rounding of accordingly.
Thanks Ruben!
can you please tell how to calculate total no.of seconds (excluding weekends).
And probably we can convert to no.of days and hours from it.
---------------
Your previous expression works but is not giving results accurately for some of the date values.
eg:
date1 & time1= 20-Aug-2016, 12:32 am
date2 & time2 = 20-Aug-2016, 1:42 am
difference of these would be 1hr 10min, this can can be rounded to 2hr
but the expression is returning as -1day 1hr.
can you please help with this , also rounding of accordingly.
Hi Prithvi, that one fails because 20/08/2016 is not a working day, so I think the hours shouldn't be counted either, this can be solved with a condition to check if there is at least 1 working day, and rounding hours up can be donde with ceil, in example:
=If(NetWorkDays('23/08/2016','23/08/2016')>0,
If(time('13:42')>Time('12:32'),
(NetWorkDays('23/08/2016','23/08/2016')-1) & 'days ' & time(Ceil('13:42'-'12:32', 1/24), 'h') & 'hr',
(NetWorkDays('23/08/2016','23/08/2016')-2) & 'days ' & time(Ceil('12:32'-'13:42', 1/24), 'h') & 'hr'), '0days 0hr')