How To Calculate Time Slots In Excel
admin 4/15/2022
Hello everyone,
This question is one that I have quite often when I do data analysis at my job :
I am very often working with data concerning time of arrival / time of departure of patients.
I want to do bar graph of the number of patients actually there during each one hour period.
Exemple :
Patient A arrival 8 am departure 12 am
Patient B arrival 10 am departure 2pm
I want a bar value 1 for 8-9, then a bar value of 1 for 9-10 then a bar value of 2 for 10-11 and a bar value of 2 for 11-12 then a bar value of 1 for 12am-1pm and bar value of 1 for 1pm-2pm.
The only way that I have managed to do that so far is to create a column for each time slot and then for each patient (each line on my excel table) with a long 'if, if if... formula' depending of course on the arrival time / departure time, plug 1 if patient is there during the time slot or nothing if he was not there, and then do a chart with this raw data.
I was wondering if there was any other way, if there is an excel time function which I could use, specifying the time slot and time of arrival/departure which could directly give '1' if patient was there ' if he was not. or a graph who could directly do that without the 24 column needed to chart a day's worth of activity.
Thank you for your help, any feedback/advice would help me a lot.
Christiane
This question is one that I have quite often when I do data analysis at my job :
I am very often working with data concerning time of arrival / time of departure of patients.
I want to do bar graph of the number of patients actually there during each one hour period.
Exemple :
Patient A arrival 8 am departure 12 am
Patient B arrival 10 am departure 2pm
I want a bar value 1 for 8-9, then a bar value of 1 for 9-10 then a bar value of 2 for 10-11 and a bar value of 2 for 11-12 then a bar value of 1 for 12am-1pm and bar value of 1 for 1pm-2pm.
The only way that I have managed to do that so far is to create a column for each time slot and then for each patient (each line on my excel table) with a long 'if, if if... formula' depending of course on the arrival time / departure time, plug 1 if patient is there during the time slot or nothing if he was not there, and then do a chart with this raw data.
I was wondering if there was any other way, if there is an excel time function which I could use, specifying the time slot and time of arrival/departure which could directly give '1' if patient was there ' if he was not. or a graph who could directly do that without the 24 column needed to chart a day's worth of activity.
Thank you for your help, any feedback/advice would help me a lot.
Christiane
Formula In Excel To Calculate Military Time
Group time by minutes intervals. If you want to group time by minutes intervals, also can use formulas. Select a cell next to the time, and type this formula =FLOOR(A2,TIME(0,15,0)), A2 is the time you use, 15 is the minutes interval, press Enter key and drag fill handle down to apply this formula to cells. Step 1 - Create hours in week and compare to time ranges. This part of the formula creates Excel date and time values based on hours, it then compares these values with the time ranges in the Excel defined Table to determine if outside range.